package jsyf.oa.dao.report.visitCustomer;

import com.alibaba.fastjson.JSONObject;
import jsyf.com.util.DateUtils;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.*;

/**
 * Created by jjx on 2018/4/5.
 */
@SuppressWarnings("all")
@Repository
public class VisitCustomerDao extends BaseDao {
    private static Integer token = 1;

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return null;
    }

    public List<JSONObject> getPeopleByUserId(String userId, String dateTime, String today, Integer type, Integer spacing) {
        int len = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "t_sys_should_holiday.SHOULD_DATE AS date ,\n" +
                "t_wrt_customer_visit.ID AS id,\n" +
                "t_wrt_customer.TEL AS tel,\n" +
                "t_wrt_customer_visit.ENTOURAGE AS entourage,\n" +
                "t_wrt_customer_visit.CUSTOMER_CREDIT as customerCredit,\n" +
                "t_wrt_customer.`NAME` AS name,\n" +
                "t_wrt_customer.WECHAT AS wechat,\n" +
                "t_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime,\n" +
                "t_wrt_customer_visit.LEAVE_TIME AS leaveTime,\n" +
                "t_wrt_customer_visit.ARRIVALS_ADDRESS AS arrivalsAddress,\n" +
                "t_wrt_customer_visit.LEAVE_ADDRESS AS leaveAddress,\n" +
                "t_wrt_customer_visit.ARRIVALS_IMG AS arrivalsImg,\n" +
                "t_wrt_customer_visit.LEAVE_IMG AS leaveImg,\n" +
                "t_wrt_customer_visit.OTHER_CONDITION AS otherCondition,\n" +
                "IFNULL(\n" +
                "\t\t\t\tCEIL(\n" +
                "\t\t\t\t\tCEILING(\n" +
                "\t\t\t\t\t\tUNIX_TIMESTAMP(t_wrt_customer_visit.LEAVE_TIME) - UNIX_TIMESTAMP(t_wrt_customer_visit.ARRIVALS_TIME)\n" +
                "\t\t\t\t\t) / 60\n" +
                "\t\t\t\t),\n" +
                "\t\t\t\t0\n" +
                "\t\t\t) AS time,\n" +
                "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                "t_wrt_customer.CUSTOMER_LEVEL AS customerLevel,\n" +
                "\tt_sys_sign_in.SIGN_IN_DISTANCE as signInDistance,\n" +
                "\tt_sys_sign_in.SIGN_OUT_DISTANCE as signOutDistance,\n" +
                "\tt_sys_sign_in.SIGN_IN_SPEED as signInSpeed,\n" +
                "\tt_sys_sign_in.SIGN_OUT_SPEED as signOutSpeed,\n" +
                "\tt_sys_sign_in.SIGN_IN_TIME_DIFF as signInTimeDiff,\n" +
                "\tt_sys_sign_in.SIGN_OUT_TIME_DIFF as signOutTimeDiff\n" +
                "FROM \n" +
                "t_sys_should_holiday \n");
        sb.append("LEFT JOIN t_sys_sign_in on t_sys_should_holiday.SHOULD_DATE = t_sys_sign_in.SIGN_DATE \n" +
                "and t_sys_sign_in.USER_ID = '" + userId + "'");
        switch (len) {
            case 7:
                sb.append("AND LEFT(t_sys_sign_in.sign_date,7) like '" + dateTime + "' \n");
                break;
            case 10:
                sb.append("AND t_sys_sign_in.sign_date='" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("and'" + dateCut + "'>=date(t_sys_sign_in.sign_date) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_sign_in.sign_date) \n");
                break;
        }
        sb.append("LEFT JOIN t_wrt_customer_visit ON t_sys_should_holiday.SHOULD_DATE = t_wrt_customer_visit.REPORT_DATE\n" +
                "AND t_wrt_customer_visit.CREATE_USER_ID = '" + userId + "' \n" +
                "AND t_wrt_customer_visit.VISIT_MODE = " + type + "\n");
        switch (len) {
            case 7:
                sb.append("AND LEFT(t_wrt_customer_visit.REPORT_DATE,7) like '" + dateTime + "' \n");
                break;
            case 10:
                sb.append("AND t_wrt_customer_visit.REPORT_DATE='" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("and'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("LEFT JOIN t_wrt_customer ON t_wrt_customer_visit.CUSTOMER_ID = t_wrt_customer.ID\n" +
                "WHERE 1=1\n"
        );
        switch (len) {
            case 7:
                sb.append("AND LEFT(t_sys_should_holiday.SHOULD_DATE,7) like '" + dateTime + "' \n");
                break;
            case 10:
                sb.append("AND t_sys_should_holiday.SHOULD_DATE='" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("and'" + dateCut + "'>=date(t_sys_should_holiday.SHOULD_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_should_holiday.SHOULD_DATE) \n");
                break;
        }
        sb.append("AND t_sys_should_holiday.FRONT_FLAG = 1\t\n" +
                "AND t_sys_should_holiday.SHOULD_DATE <='" + today + "'\t\n" +
                "ORDER BY t_sys_should_holiday.SHOULD_DATE DESC,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME ,\n" +
                "\tt_wrt_customer_visit.LEAVE_TIME DESC\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public JSONObject getPeopleInfoByUserId(String userId, String targetDate, Integer type, Integer spacing) {
        int len = targetDate.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ");
        if (type.equals(token)) {
            sb.append("(SELECT SUM(FACE_TARGET) FROM t_sys_target ");
        } else {
            sb.append("(SELECT SUM(TEL_TARGET) FROM t_sys_target ");
        }
        sb.append("WHERE USER_ID='" + userId + "' AND ");
        switch (len) {
            case 7:
                sb.append("LEFT(TARGET_DATE,7)='" + targetDate + "'");
                break;
            case 10:
                sb.append("TARGET_DATE='" + targetDate + "' \n");
                break;
            case 12:
                dateCut = targetDate.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(TARGET_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(TARGET_DATE) \n");
                break;
        }
        sb.append(" ) as dayTarget,");
        sb.append("(SELECT COUNT(1) FROM ");
        sb.append("t_wrt_customer_visit WHERE ");
        sb.append("CREATE_USER_ID='" + userId + "' AND ");
        sb.append("VISIT_MODE = " + type + " AND ");
        switch (len) {
            case 7:
                sb.append("LEFT(REPORT_DATE,7)='" + targetDate + "'");
                break;
            case 10:
                sb.append("REPORT_DATE='" + targetDate + "'");
                break;
            case 12:
                dateCut = targetDate.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(REPORT_DATE) \n");
                break;
        }
        sb.append(") as complete ");
        JSONObject jsonObject = super.queryForJsonObject(sb.toString());
        return jsonObject;
    }

    public List<JSONObject> getRegionByUserId(String userId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT REGION_ID AS regionId FROM t_sys_user_2_region WHERE  \n");
        sb.append(" USER_ID =" + "'" + userId + "'");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public JSONObject getByFaceInfo(String dateTime, String todayTime, Integer spacing, String userId) {
        int len = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT \n");
        sb.append("(select \n");
        sb.append("SUM(t_sys_target.TEL_TARGET) \n");
        sb.append("from t_sys_target \n");
        sb.append("LEFT JOIN v_sys_user ON v_sys_user.companyId=t_sys_target.COMPANY_ID \n");
        sb.append("where 1=1 \n");
        sb.append("AND v_sys_user.id ='" + userId + "' \n");
        switch (len) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("and TARGET_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("and LEFT(TARGET_DATE,7) = '" + dateTime.substring(0, 7) + "' \n");
                }
                break;
            case 10:
                sb.append("and TARGET_DATE='" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("and'" + dateCut + "'>=date(TARGET_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(TARGET_DATE) \n");
                break;
        }
        sb.append(") AS targetDay,  (SELECT count(*) FROM t_wrt_customer_visit \n");
        sb.append(" LEFT JOIN v_sys_user ON t_wrt_customer_visit.CREATE_USER_ID=v_sys_user.id \n");
        sb.append(" WHERE 1=1  AND t_wrt_customer_visit.VISIT_MODE =0  \n");
        switch (len) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("and t_wrt_customer_visit.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("and LEFT(t_wrt_customer_visit.REPORT_DATE,7) = '" + dateTime.substring(0, 7) + "' \n");
                }
                break;
            case 10:
                sb.append("  AND t_wrt_customer_visit.REPORT_DATE='" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("and'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append(" AND t_wrt_customer_visit.COMPANY_ID =(");
        sb.append(" select v_sys_user.companyId");
        sb.append(" from v_sys_user WHERE v_sys_user.ID='" + userId + "'");
        sb.append(")  AND v_sys_user.roleName in ('区域经理','区域组长')) AS finishDay");
        JSONObject jsonObject = queryForJsonObject(sb.toString());
        return jsonObject == null ? null : jsonObject;
    }

    public JSONObject getByTelInfo(String dateTime, String todayTime, Integer spacing, String userId) {
        int len = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT \n");
        sb.append("(select \n");
        sb.append("SUM(t_sys_target.FACE_TARGET) \n");
        sb.append("from t_sys_target \n");
        sb.append("LEFT JOIN v_sys_user ON v_sys_user.companyId=t_sys_target.COMPANY_ID \n");
        sb.append("where 1=1 \n");
        sb.append("AND v_sys_user.id ='" + userId + "' \n");
        switch (len) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("and TARGET_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("AND  LEFT(TARGET_DATE,7) ='" + dateTime.substring(0, 7) + "' \n");
                }
                break;
            case 10:
                sb.append("and TARGET_DATE='" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("and'" + dateCut + "'>=date(TARGET_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(TARGET_DATE) \n");
                break;
        }
        sb.append(") AS targetDay,  (SELECT count(*) FROM t_wrt_customer_visit \n");
        sb.append(" LEFT JOIN v_sys_user ON t_wrt_customer_visit.CREATE_USER_ID=v_sys_user.id \n");
        sb.append(" WHERE 1=1  AND t_wrt_customer_visit.VISIT_MODE =1  \n");
        switch (len) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("and t_wrt_customer_visit.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("and LEFT(t_wrt_customer_visit.REPORT_DATE,7) ='" + dateTime.substring(0, 7) + "' \n");
                }
                break;
            case 10:
                sb.append("  AND t_wrt_customer_visit.REPORT_DATE='" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("and'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append(" AND t_wrt_customer_visit.COMPANY_ID =(");
        sb.append(" select v_sys_user.companyId");
        sb.append(" from v_sys_user WHERE v_sys_user.ID='" + userId + "'");
        sb.append(")  AND v_sys_user.roleName in ('区域经理','区域组长')) AS finishDay");
        JSONObject jsonObject = queryForJsonObject(sb.toString());
        return jsonObject == null ? null : jsonObject;
    }

    public List<JSONObject> getForPersonalByUserId(String target, Integer type, String dateTime, Integer spacing, String userId, String firstTime, String todayTime) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT \n" +
                "DISTINCT obj.*, CASE \n" +
                "WHEN @rowtotal = obj.averageComplete THEN \n" +
                "@rownum \n" +
                "WHEN @rowtotal := obj.averageComplete THEN \n" +
                "@rownum :=@rownum + 1 \n" +
                "WHEN @rowtotal = 0 THEN \n" +
                "@rownum :=@rownum + 1 \n" +
                "END AS rownum \n" +
                "FROM( \n" +
                "SELECT b_sys_user_for_report.userId,b_sys_user_for_report.userName,b_sys_user_for_report.regionId,t_sys_region.name as regionName," +
                "IFNULL(b_sys_target.target, 0) AS target ,\n" +
                "IFNULL(t_sys_complete.complete, 0) AS complete, IFNULL(t_sys_sign.sign, 0) AS signManagerNum,\n" +
                "IFNULL(TRUNCATE (complete / sign, 2),0.00)  AS averageComplete,\n" +
                "IFNULL(TRUNCATE(complete/target*100,2),0.00) as  completionRate\n" +
                "FROM (SELECT v_sys_part_time_for_report.userId ,v_sys_part_time_for_report.userName,v_sys_part_time_for_report.entryDate\n" +
                ",t_sys_user_leave.LEAVE_DATE as leaveDate,CASE\n" +
                "\tWHEN t_sys_role_name.FRONT_FLAG = 1 AND AREA_TYPE = 1\n" +
                "\t\tTHEN \n" +
                "\t\t\t(SELECT t_sys_region.`id` FROM t_sys_region \n" +
                "\t\t\t\t\t\t\t\tWHERE DEL_FLAG = 0 \n" +
                "\t\t\t\t\t\t\t\t\tAND PARENT_ID = '0' \n" +
                "\t\t\t\t\t\t\t\t\tAND ID in(SELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = v_sys_part_time_for_report.userID) LIMIT 0,1) \n" +
                "\tWHEN t_sys_role_name.FRONT_FLAG = 1 AND AREA_TYPE = 0\n" +
                "\t\tTHEN\n" +
                "\t\t\t(SELECT t_sys_region.id FROM t_sys_region \n" +
                "\t\t\t\t\t\t\t\t\tWHERE DEL_FLAG = 0 \n" +
                "\t\t\t\t\t\t\t\t\t\tAND PARENT_ID <> '0' \n" +
                "\t\t\t\t\t\t\t\t\t\tAND ID in(SELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID = v_sys_part_time_for_report.userID) LIMIT 0,1)\n" +
                "\tELSE '0'\n" +
                "END as regionId\n" +
                "FROM v_sys_part_time_for_report  LEFT JOIN t_sys_role_name on v_sys_part_time_for_report.roleNameId=t_sys_role_name.ID\n" +
                "LEFT JOIN t_sys_user_leave ON v_sys_part_time_for_report.userId=t_sys_user_leave.USER_ID\n" +
                "WHERE v_sys_part_time_for_report.departId in( SELECT v_sys_part_time_for_report.departId  \n" +
                "FROM v_sys_part_time_for_report\n" +
                "WHERE v_sys_part_time_for_report.userId ='" + userId + "')\n" +
                "AND t_sys_role_name.FRONT_FLAG =1 AND t_sys_role_name.AREA_TYPE=0\n");
        //sb.append("WHERE v_sys_user_for_report.userId ='" + userId + "')\n");
        sb.append(") AS b_sys_user_for_report\n" +
                "LEFT JOIN (  SELECT  USER_ID, SUM(" + target + ") AS target  FROM  t_sys_target  WHERE\n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" TARGET_DATE BETWEEN'" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("TARGET_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("TARGET_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(TARGET_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(TARGET_DATE) \n");
                break;
        }
        sb.append("GROUP BY  USER_ID  ) b_sys_target ON b_sys_user_for_report.userId = b_sys_target.USER_ID \n" +
                "LEFT JOIN ( SELECT CREATE_USER_ID, SUM(VISIT_MODE = " + type + ") AS complete FROM t_wrt_customer_visit  WHERE\n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" REPORT_DATE BETWEEN'" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(REPORT_DATE) \n");
                break;
        }
        sb.append("GROUP BY CREATE_USER_ID ) AS t_sys_complete ON b_sys_user_for_report.userId = t_sys_complete.CREATE_USER_ID\n" +
                "LEFT JOIN ( SELECT USER_ID, COUNT(t_sys_sign_in.SIGN_DATE) AS sign FROM t_sys_sign_in WHERE \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" SIGN_DATE BETWEEN'" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("SIGN_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("SIGN_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(SIGN_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(SIGN_DATE) \n");
                break;
        }
        sb.append("GROUP BY USER_ID ) AS t_sys_sign ON t_sys_sign.USER_ID = b_sys_user_for_report.userId\n" +
                "LEFT JOIN t_sys_region on t_sys_region.id =b_sys_user_for_report.regionId\n" +
                "WHERE 1=1 AND (b_sys_user_for_report.leaveDate is  NULL OR\n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" b_sys_user_for_report.leaveDate BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("b_sys_user_for_report.leaveDate LIKE '" + dateTime + "%' \n");
                    int year = Integer.parseInt(todayTime.substring(0, 4));
                    int month = Integer.parseInt(todayTime.substring(5, 7));
                    sb.append("and b_sys_user_for_report.entryDate <= '" + year + "-" + month + "-" + DateUtils.getLastDayOfMonth(year, month) + "' \n");

                }
                break;
            case 10:
                sb.append("b_sys_user_for_report.leaveDate > '" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(b_sys_user_for_report.leaveDate) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(b_sys_user_for_report.leaveDate) \n");
                break;
        }
        sb.append(")ORDER BY averageComplete DESC) AS obj, \n" +
                "(SELECT @rownum := 0 ,@rowtotal := NULL ) r \n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getForDepart(String target, Integer type, String dateTime, Integer spacing, String userId, String todayTime, String companyId) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT obj.* , CASE \n" +
                " WHEN @rowtotal = obj.averageComplete THEN \n" +
                "  @rownum \n" +
                " WHEN @rowtotal := obj.averageComplete THEN \n" +
                " @rownum :=@rownum + 1 \n" +
                " WHEN @rowtotal = 0 THEN \n" +
                " @rownum :=@rownum + 1 \n" +
                "  END AS rownum \n" +
                "  FROM( \n");
        sb.append("SELECT depart.*,bigManager.bigManagerName,bigManager.regionId,IFNULL(visit.complete,0) as complete,IFNULL(target.target,0) as target,\n" +
                "IFNULL(userNow.regionManagerNum,0) as regionManagerNum,IFNULL(userTarget.managerTargetNum,0) as  managerTargetNum ,IFNULL(leaveManager.leaveNumber,0) as leaveNumber\n");
        if (length == 10) {
            sb.append(",(select IFNULL(TRUNCATE(complete/(regionManagerNum-IFNULL(leaveNumber,0)),2),0.00)) as  averageComplete\n");
        } else {
            sb.append(",(select IFNULL(TRUNCATE(complete/regionManagerNum,2),0.00)) as  averageComplete\n");
        }
        sb.append(",(select IFNULL(TRUNCATE(complete/target*100,2),0.00)) as  completionRate\n" +
                "FROM (SELECT t_sys_depart.COMPANY_ID as companyId,t_sys_depart.ID as departId,replace(t_sys_depart.`NAME`,'大区','') as departName\n" +
                "\tFROM t_sys_depart WHERE 1=1 AND `NAME` LIKE '%大区%' AND DEL_FLAG = 0 ) as depart LEFT JOIN \n" +
                "(\n" +
                "SELECT v_sys_part_time_for_report.departId,v_sys_part_time_for_report.userName as bigManagerName\n" +
                ",CASE\n" +
                "WHEN t_sys_role_name.FRONT_FLAG = 1\n" +
                "AND AREA_TYPE = 1 THEN\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_region\n" +
                "\t\tWHERE\n" +
                "\t\t\tDEL_FLAG = 0\n" +
                "\t\tAND PARENT_ID = '0'\n" +
                "\t\tAND ID IN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tREGION_ID\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_sys_user_2_region\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\tUSER_ID = v_sys_part_time_for_report.userID\n" +
                "\t\t)\n" +
                "\t\tLIMIT 0,\n" +
                "\t\t1\n" +
                "\t)\n" +
                "WHEN t_sys_role_name.FRONT_FLAG = 1\n" +
                "AND AREA_TYPE = 0 THEN\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_region.ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_region\n" +
                "\t\tWHERE\n" +
                "\t\t\tDEL_FLAG = 0\n" +
                "\t\tAND PARENT_ID <> '0'\n" +
                "\t\tAND ID IN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tREGION_ID\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_sys_user_2_region\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\tUSER_ID = v_sys_part_time_for_report.userID\n" +
                "\t\t)\n" +
                "\t\tLIMIT 0,\n" +
                "\t\t1\n" +
                "\t)\n" +
                "ELSE\n" +
                "\t'0'\n" +
                "END AS regionId\n" +
                "FROM v_sys_part_time_for_report\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=v_sys_part_time_for_report.roleNameId\n" +
                "WHERE 1=1\n" +
                "AND t_sys_role_name.`NAME` ='大区经理'\n" +
                "AND v_sys_part_time_for_report.jobState!=1\n" +
                "AND v_sys_part_time_for_report.companyId  in (\n");
        if (companyId == null) {
            sb.append("SELECT v_sys_part_time_for_report.companyId\n" +
                    "FROM v_sys_part_time_for_report\n" +
                    "WHERE v_sys_part_time_for_report.userId ='" + userId + "')\n");
        } else {
            sb.append("'" + companyId + "') \n");
        }
        sb.append("GROUP BY departId" +
                ") as bigManager ON depart.departId = bigManager.departId LEFT JOIN \n" +
                "(SELECT t_sys_depart.ID as departId,COUNT(1) as complete from t_wrt_customer_visit \n" +
                "\tLEFT JOIN t_sys_user ON t_wrt_customer_visit.CREATE_USER_ID = t_sys_user.ID LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "  LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE 1=1 AND  \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" REPORT_DATE BETWEEN'" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(REPORT_DATE) \n");
                break;
        }
        sb.append("AND t_sys_role_name.`NAME` in ('区域经理','区域组长') and t_wrt_customer_visit.VISIT_MODE = " + type + "  \n" +
                "  GROUP BY t_sys_depart.ID ) as visit ON depart.departId = visit.departId\n" +
                "LEFT JOIN (\tSELECT t_sys_depart.id as departId,SUM(t_sys_target." + target + ") as target FROM t_sys_target\n" +
                "\tLEFT JOIN t_sys_user ON t_sys_target.USER_ID = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "  LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE 1=1 AND t_sys_role_name.`NAME` in ('区域经理','区域组长')  \tAND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" t_sys_target.TARGET_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_sys_target.TARGET_DATE  LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_sys_target.TARGET_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_sys_target.TARGET_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_target.TARGET_DATE) \n");
                break;
        }
        sb.append("\tGROUP BY t_sys_depart.ID ) as target ON depart.departId = target.departId LEFT JOIN  \n" +
                "(SELECT t_sys_depart.ID as departId, \n" +
                "\tt_sys_role.TARGET_NUMBER as managerTargetNum FROM t_sys_role LEFT JOIN t_sys_depart \n" +
                "ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_role_name on t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID \n" +
                "\tWHERE 1=1  AND t_sys_role_name.`NAME` in ('区域经理','区域组长') \n" +
                "\tAND t_sys_depart.`NAME` like '%大区%' AND t_sys_depart.DEL_FLAG = 0 and t_sys_role.DEL_FLAG = 0 " +
                "and t_sys_role_name.DEL_FLAG = 0 ) as userTarget ON depart.departId = userTarget.departId LEFT JOIN \n" +
                "(SELECT departId, COUNT(1) as regionManagerNum FROM v_sys_user_for_report \n" +
                "WHERE 1=1  AND roleName in ('区域经理','区域组长') AND (v_sys_user_for_report.leaveDate IS NULL OR \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" v_sys_user_for_report.leaveDate BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("v_sys_user_for_report.leaveDate LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("v_sys_user_for_report.leaveDate > '" + dateTime + "' \n");
                int year = Integer.parseInt(todayTime.substring(0, 4));
                int month = Integer.parseInt(todayTime.substring(5, 7));
                sb.append("and v_sys_user_for_report.entryDate <= '" + year + "-" + month + "-" + DateUtils.getLastDayOfMonth(year, month) + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(v_sys_user_for_report.leaveDate) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(v_sys_user_for_report.leaveDate) \n");
                break;
        }
        sb.append(") AND v_sys_user_for_report.userId is not null  GROUP BY departId ) as userNow ON depart.departId = userNow.departId \n" +
                "LEFT JOIN (SELECT t_sys_role.DEPART_ID as departId,COUNT(1) as leaveNumber \n" +
                "FROM (SELECT t_sys_leave.USER_ID as userId,COUNT(1) as leaveCount FROM t_sys_leave WHERE\n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" LEAVE_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("LEAVE_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("LEAVE_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(LEAVE_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(LEAVE_DATE) \n");
                break;
        }
        sb.append("\tGROUP BY t_sys_leave.USER_ID) as signLeave LEFT JOIN t_sys_user ON signLeave.userId = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "WHERE t_sys_role_name.`NAME` in ('区域经理','区域组长') GROUP BY t_sys_role.DEPART_ID\n" +
                " )as leaveManager ON depart.departId = leaveManager.departId \n");
        if (companyId == null) {
            sb.append("WHERE 1=1 AND companyId in (SELECT companyId FROM v_sys_user_for_report WHERE userid in ('" + userId + "')) \n");
        } else {
            sb.append("WHERE 1=1 AND companyId in ('" + companyId + "') \n");
        }
        sb.append("Group BY departId \n");
        sb.append("ORDER BY averageComplete DESC ) as obj,   (SELECT @rownum := 0 ,@rowtotal := NULL) r  \n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getForGroupByUserId(String target, Integer type, String dateTime, Integer spacing, String userId, String todayTime, String companyId, String condition) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT obj.* , CASE \n" +
                " WHEN @rowtotal = obj.averageComplete THEN \n" +
                "  @rownum \n" +
                " WHEN @rowtotal := obj.averageComplete THEN \n" +
                " @rownum :=@rownum + 1 \n" +
                " WHEN @rowtotal = 0 THEN \n" +
                " @rownum :=@rownum + 1 \n" +
                "  END AS rownum \n" +
                "  FROM( \n" +
                "SELECT company.*,managerName.managerName,managerName.salerManagerName,IFNULL(visit.complete,0) as complete,IFNULL(target.target,0) as target,\n" +
                "IFNULL(userNow.regionManagerNum,0) as regionManagerNum,IFNULL(userTarget.managerTargetNum,0) as  managerTargetNum ,IFNULL(leaveManager.leaveNumber,0) as leaveNumber\n" +
                " ,(select IFNULL(TRUNCATE(complete/target*100,2),0.00)) as  completionRate\n");
        if (length == 10) {
            sb.append(",(select IFNULL(TRUNCATE(complete/(regionManagerNum-IFNULL(leaveNumber,0)),2),0.00)) as  averageComplete\n");
        } else {
            sb.append(",(select IFNULL(TRUNCATE(complete/regionManagerNum,2),0.00)) as  averageComplete\n");

        }
        sb.append("FROM  (SELECT t_sys_company.id AS companyId , t_sys_company.`NAME` as companyName \n" +
                "\tFROM t_sys_company \n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.COMPANY_ID=t_sys_company.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.DEPART_ID=t_sys_depart.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE 1=1 AND  t_sys_depart.DEL_FLAG = 0 and t_sys_role.DEL_FLAG = 0 and t_sys_role_name.DEL_FLAG = 0 AND t_sys_company.DEL_FLAG = 0\n" +
                "AND t_sys_role_name.FRONT_FLAG =1 GROUP BY COMPANY_ID ) as company LEFT JOIN \n");
        sb.append("(SELECT v_sys_company.companyId,v_sys_company.managerName,v_sys_company.salerManagerName \n" +
                "FROM v_sys_company\n" +
                "WHERE 1=1 AND v_sys_company.companyDelFlag =0\n" +
                ") as managerName ON company.companyId  = managerName.companyId  \n");
        sb.append(" LEFT JOIN  (SELECT t_sys_company.ID as companyId,COUNT(1) as complete from t_wrt_customer_visit \n" +
                "\tLEFT JOIN t_sys_user ON t_wrt_customer_visit.CREATE_USER_ID = t_sys_user.ID LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "  LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID \n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE 1=1 AND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" t_wrt_customer_visit.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("AND t_sys_role_name.`NAME` in ('区域经理','区域组长') and t_wrt_customer_visit.VISIT_MODE =" + type + "\n" +
                "  GROUP BY t_sys_company.ID ) as visit ON company.companyId = visit.companyId\n" +
                "LEFT JOIN (\tSELECT t_sys_company.id as companyId,SUM(t_sys_target." + target + ") as target FROM t_sys_target\n" +
                "\tLEFT JOIN t_sys_user ON t_sys_target.USER_ID = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "  LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\tWHERE 1=1 AND t_sys_depart.DEL_FLAG = 0 and t_sys_role.DEL_FLAG = 0 and t_sys_role_name.DEL_FLAG = 0 AND t_sys_company.DEL_FLAG = 0\n" +
                "\tAND t_sys_role_name.`NAME` in ('区域经理','区域组长') AND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" t_sys_target.TARGET_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_sys_target.TARGET_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_sys_target.TARGET_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_sys_target.TARGET_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_target.TARGET_DATE) \n");
                break;
        }
        sb.append("GROUP BY t_sys_company.ID ) as target ON company.companyId = target.companyId\n" +
                "LEFT JOIN  (SELECT t_sys_company.ID as companyId, \n" +
                "\tsum(t_sys_role.TARGET_NUMBER) as managerTargetNum FROM t_sys_role LEFT JOIN t_sys_depart \n" +
                "ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_role_name on t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID \n" +
                "LEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\tWHERE 1=1 AND t_sys_role_name.`NAME` in ('区域经理','区域组长') \n" +
                "AND t_sys_depart.DEL_FLAG = 0 and t_sys_role.DEL_FLAG = 0 and t_sys_role_name.DEL_FLAG = 0 AND t_sys_company.DEL_FLAG = 0\n" +
                "GROUP BY t_sys_company.ID \n" +
                ") as userTarget ON company.companyId = userTarget.companyId \n" +
                "LEFT JOIN \n" +
                "(SELECT v_sys_user_for_report.companyId AS companyId, COUNT(1) as regionManagerNum FROM v_sys_user_for_report \n" +
                "WHERE 1=1 AND roleName in ('区域经理','区域组长') AND (v_sys_user_for_report.leaveDate IS NULL OR \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" v_sys_user_for_report.leaveDate BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("v_sys_user_for_report.leaveDate LIKE '" + dateTime + "%' \n");
                    int year = Integer.parseInt(todayTime.substring(0, 4));
                    int month = Integer.parseInt(todayTime.substring(5, 7));
                    sb.append("and v_sys_user_for_report.entryDate <= '" + year + "-" + month + "-" + DateUtils.getLastDayOfMonth(year, month) + "' \n");
                }
                break;
            case 10:
                sb.append("v_sys_user_for_report.leaveDate > '" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(v_sys_user_for_report.leaveDate) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(v_sys_user_for_report.leaveDate) \n");
                break;
        }
        sb.append(")  AND v_sys_user_for_report.userId is not null  GROUP BY v_sys_user_for_report.companyId) as userNow ON company.companyId = userNow.companyId \n" +
                "LEFT JOIN (SELECT t_sys_company.id as companyId,COUNT(1) as leaveNumber FROM \n" +
                "\t(SELECT t_sys_leave.USER_ID as userId,COUNT(1) as leaveCount FROM t_sys_leave WHERE 1=1 AND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_sys_leave.LEAVE_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_sys_leave.LEAVE_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_sys_leave.LEAVE_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_sys_leave.LEAVE_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_leave.LEAVE_DATE) \n");
                break;
        }
        sb.append("GROUP BY t_sys_leave.USER_ID ) as signLeave\n" +
                "\tLEFT JOIN t_sys_user ON signLeave.userId = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "WHERE t_sys_role_name.`NAME` in ('区域经理','区域组长') \n" +
                "AND t_sys_depart.DEL_FLAG = 0 and t_sys_role.DEL_FLAG = 0 and t_sys_role_name.DEL_FLAG = 0 AND t_sys_company.DEL_FLAG = 0\n" +
                "\tGROUP BY t_sys_company.ID\n" +
                " )as leaveManager ON company.companyId = leaveManager.companyId \n" +
                "ORDER BY averageComplete DESC ) as obj,   (SELECT @rownum := 0 ,@rowtotal := NULL) r \n");
        sb.append(condition);
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getAllManagerByCompanyId(String target, Integer type, String dateTime, Integer spacing, String todayTime, String companyId) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT \n" +
                "DISTINCT obj.*, CASE \n" +
                "WHEN @rowtotal = obj.completionRate THEN \n" +
                "@rownum \n" +
                "WHEN @rowtotal := obj.completionRate THEN \n" +
                "@rownum :=@rownum + 1 \n" +
                "WHEN @rowtotal = 0 THEN \n" +
                "@rownum :=@rownum + 1 \n" +
                "END AS rownum \n" +
                "FROM( \n" +
                "SELECT depart.regionId , depart.departName ,depart.managerName,depart.bigManagerName,depart.userId,\n" +
                "sysSign.signInDistance,\n" +
                "sysSign.signOutDistance,\n" +
                "sysSign.signInSpeed,\n" +
                "sysSign.signOutSpeed,\n" +
                "sysSign.signInTimeDiff,\n" +
                "sysSign.signOutTimeDiff,\n" +
                "sysSign.signTime,\n" +
                "sysSign.signOutTime,\n" +
                "visitLeave.leaveTime,\n" +
                "visitArrivals.arrivalsTime,\n" +
                "IFNULL(sysSignNum.ycNum,0) as ycNum,\n" +
                "IFNULL(b_sys_target.target, 0) AS target, \n" +
                "IFNULL(t_sys_complete.complete, 0) AS complete, \n" +
                "IFNULL(t_sys_sign.sign, 0) AS signManagerNum,\n" +
                "(SELECT \n" +
                "IFNULL(TRUNCATE (complete / sign, 2),0.00) \n" +
                ") AS averageComplete, \n" +
                "(select IFNULL(TRUNCATE(complete/target*100,2),0.00)) as  completionRate\n" +
                "FROM \n" +
                "(SELECT obj.*,sysDepart.`userName` as bigManagerName\n" +
                "FROM(\n" +
                "SELECT v_sys_part_time_for_report.userName as managerName,v_sys_part_time_for_report.userId as userId ," +
                "t_sys_depart.`NAME` as departName,t_sys_region.ID AS regionId,v_sys_part_time_for_report.parentId as parentId\n" +
                ",v_sys_part_time_for_report.departId\n" +
                "FROM v_sys_part_time_for_report LEFT JOIN t_sys_depart on t_sys_depart.ID=.v_sys_part_time_for_report.departId\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID= v_sys_part_time_for_report.userId\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId=t_sys_role_name.ID\n" +
                "LEFT JOIN  t_sys_user_leave ON t_sys_user_leave.USER_ID=v_sys_part_time_for_report.userId\n" +
                "WHERE 1=1 and t_sys_depart.DEL_FLAG =0 and t_sys_region.DEL_FLAG=0 and t_sys_role_name.DEL_FLAG=0\n" +
                "and t_sys_region.PARENT_ID=0 AND v_sys_part_time_for_report.companyId='" + companyId + "'\n" +
                "AND (t_sys_role_name.`NAME` in('区域经理','区域组长') AND (t_sys_user_leave.LEAVE_DATE IS NULL OR \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_sys_user_leave.LEAVE_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_sys_user_leave.LEAVE_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_sys_user_leave.LEAVE_DATE > '" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_sys_user_leave.LEAVE_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_user_leave.LEAVE_DATE) \n");
                break;
        }
        sb.append("))) as obj\n" +
                "LEFT JOIN (SELECT v_sys_part_time_for_report.userName,v_sys_part_time_for_report.departId\n" +
                "FROM v_sys_part_time_for_report\n" +
                "LEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId=t_sys_role_name.ID\n" +
                "WHERE 1=1\n" +
                "AND t_sys_role_name.`NAME` ='大区经理'\n" +
                "AND v_sys_part_time_for_report.companyId='" + companyId + "'\n" +
                "AND v_sys_part_time_for_report.userName is not null\n" +
                "AND v_sys_part_time_for_report.jobState !=1) as sysDepart\n" +
                "ON sysDepart.departId=obj.departId\n" +
                "GROUP BY obj.userId  ) as depart \n" +
                "LEFT JOIN ( SELECT  USER_ID, SUM(" + target + ") AS target  FROM  t_sys_target  WHERE  \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("TARGET_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("TARGET_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("TARGET_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(TARGET_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(TARGET_DATE) \n");
                break;
        }
        sb.append("GROUP BY  USER_ID ) b_sys_target ON depart.userId = b_sys_target.USER_ID  \n" +
                "LEFT JOIN (  SELECT  CREATE_USER_ID,  SUM(VISIT_MODE = " + type + ") AS complete \n" +
                "FROM  t_wrt_customer_visit WHERE \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(REPORT_DATE) \n");
                break;
        }
        sb.append("GROUP BY CREATE_USER_ID ) AS t_sys_complete ON depart.userId = t_sys_complete.CREATE_USER_ID\n" +
                "LEFT JOIN ( SELECT  USER_ID, COUNT(t_sys_sign_in.SIGN_DATE) AS sign FROM  t_sys_sign_in  WHERE \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("SIGN_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("SIGN_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("SIGN_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(SIGN_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(SIGN_DATE) \n");
                break;
        }
        sb.append("GROUP BY  USER_ID ) AS t_sys_sign ON t_sys_sign.USER_ID = depart.userId \n");
        sb.append("LEFT JOIN (SELECT USER_ID ,\n" +
                "t_sys_sign_in.CREATE_TIME as signTime,\n" +
                "t_sys_sign_in.SIGN_OUT_TIME as signOutTime,\n" +
                "t_sys_sign_in.SIGN_IN_DISTANCE\tas\tsignInDistance,\n" +
                "t_sys_sign_in.SIGN_OUT_DISTANCE\tas\tsignOutDistance,\n" +
                "t_sys_sign_in.SIGN_IN_SPEED\tas\tsignInSpeed,\n" +
                "t_sys_sign_in.SIGN_OUT_SPEED\tas\tsignOutSpeed,\n" +
                "t_sys_sign_in.SIGN_IN_TIME_DIFF\tas\tsignInTimeDiff,\n" +
                "t_sys_sign_in.SIGN_OUT_TIME_DIFF\tas\tsignOutTimeDiff\n" +
                "FROM t_sys_sign_in \n" +
                "WHERE 1=1 and\n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("SIGN_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("SIGN_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("SIGN_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(SIGN_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(SIGN_DATE) \n");
                break;
        }
        sb.append("GROUP BY  USER_ID\n" +
                ") as sysSign ON  depart.userId =sysSign.USER_ID\n" +
                "LEFT JOIN (SELECT USER_ID ,\n" +
                "COUNT(1) as ycNum\n" +
                "FROM t_sys_sign_in \n" +
                "WHERE 1=1 AND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("SIGN_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("SIGN_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("SIGN_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(SIGN_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(SIGN_DATE) \n");
                break;
        }
        sb.append("AND (\n" +
                "REPLACE(t_sys_sign_in.SIGN_IN_DISTANCE,'公里','')>=15\n" +
                "OR REPLACE(t_sys_sign_in.SIGN_OUT_DISTANCE,'公里','')>=15\n" +
                "OR substring(t_sys_sign_in.SIGN_IN_TIME_DIFF,locate('时',t_sys_sign_in.SIGN_IN_TIME_DIFF)+1,2)>=30\n" +
                "OR substring(t_sys_sign_in.SIGN_OUT_TIME_DIFF,locate('时',t_sys_sign_in.SIGN_OUT_TIME_DIFF)+1,2)>=30\n" +
                ")GROUP BY  USER_ID\n" +
                ") as sysSignNum ON  depart.userId =sysSignNum.USER_ID\n");
        sb.append("LEFT JOIN (\n" +
                "SELECT\n" +
                "\tt_wrt_customer_visit.CREATE_USER_ID AS userId,\n" +
                "\tmax(\n" +
                "\t\tt_wrt_customer_visit.LEAVE_TIME\n" +
                "\t) AS leaveTime\n" +
                "FROM\n" +
                "\tt_wrt_customer_visit\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_wrt_customer_visit.VISIT_MODE = " + type + "\n");
        sb.append("AND\n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_wrt_customer_visit.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("AND t_wrt_customer_visit.LEAVE_TIME IS NOT NULL\n" +
                "GROUP BY\n" +
                "\tt_wrt_customer_visit.CREATE_USER_ID) as visitLeave ON visitLeave.userId=depart.userId\n");
        sb.append("LEFT JOIN (\n" +
                "SELECT\n" +
                "\tt_wrt_customer_visit.CREATE_USER_ID AS userId,\n" +
                "\tmin(\n" +
                "\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t) AS arrivalsTime\n" +
                "FROM\n" +
                "\tt_wrt_customer_visit\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_wrt_customer_visit.VISIT_MODE = " + type + "\n");
        sb.append("AND\n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_wrt_customer_visit.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("GROUP BY\n" +
                "\tt_wrt_customer_visit.CREATE_USER_ID) as visitArrivals ON visitArrivals.userId=depart.userId\n");
        sb.append("ORDER BY completionRate desc,signTime desc\n" +
                ") AS obj, (SELECT @rownum := 0 ,@rowtotal := NULL ) r \n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getManagerByUserId(String dateTime, Integer spacing, String todayTime, String userId, String companyId) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT \n" +
                "DISTINCT obj.*, CASE \n" +
                "WHEN @rowtotal = obj.countNo THEN \n" +
                "@rownum \n" +
                "WHEN @rowtotal := obj.countNo THEN \n" +
                "@rownum :=@rownum + 1 \n" +
                "WHEN @rowtotal = 0 THEN \n" +
                "@rownum :=@rownum + 1 \n" +
                "END AS rownum \n" +
                "FROM( \n" +
                "SELECT tmp.* FROM(\n" +
                "SELECT visit.userId,visit.managerName,REPLACE(visit.`departName`,'大区','') AS departName,visit.entourageName,jobState.jobState,visit.visitCount,regionName.name AS regionName,visit.companyId\n" +
                ",countNo.countNo,region.id as regionId\n" +
                "FROM(SELECT t_sys_user.ID AS userId,t_sys_user.`NAME` AS managerName,t_sys_depart.`NAME` AS departName,t_sys_company.ID AS companyId,t_sys_depart.id as departId,\n" +
                "t_wrt_customer_visit.ENTOURAGE AS entourageName,t_wrt_customer_visit.ENTOURAGE_ID,COUNT(1) AS visitCount\n" +
                "FROM t_wrt_customer_visit \n" +
                "LEFT JOIN t_sys_user on t_sys_user.ID=t_wrt_customer_visit.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role on t_sys_role.id=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name on t_sys_role.ROLE_NAME_ID=t_sys_role_name.id\n" +
                "LEFT JOIN t_sys_depart on t_sys_depart.id=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company on t_sys_depart.COMPANY_ID=t_sys_company.id\n" +
                "LEFT JOIN t_sys_user_leave on t_sys_user_leave.USER_ID=t_sys_user.ID\n" +
                "WHERE t_sys_role_name.`NAME`= '大区经理' AND t_wrt_customer_visit.VISIT_MODE=1 \n");
        sb.append("and \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_wrt_customer_visit.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }

        if (companyId == null) {
            sb.append("AND t_sys_company.id in (SELECT v_sys_user.companyId FROM v_sys_user WHERE v_sys_user.id = '" + userId + "' )\n");
        } else {
            sb.append(" AND t_sys_company.id in ('" + companyId + "') \n");
        }
        sb.append("AND (t_sys_user_leave.LEAVE_DATE IS NULL OR \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_sys_user_leave.LEAVE_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_sys_user_leave.LEAVE_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_sys_user_leave.LEAVE_DATE > '" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_sys_user_leave.LEAVE_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_user_leave.LEAVE_DATE) \n");
                break;
        }
        sb.append(")GROUP BY t_wrt_customer_visit.CREATE_USER_ID,t_wrt_customer_visit.ENTOURAGE_ID\n" +
                ") AS visit LEFT JOIN(\n" +
                "SELECT t_sys_user_2_region.USER_ID,t_sys_region.`NAME` \n" +
                "FROM t_sys_user_2_region LEFT JOIN t_sys_region on t_sys_region.ID =t_sys_user_2_region.REGION_ID\n" +
                "WHERE t_sys_region.DEL_FLAG =0 AND t_sys_region.PARENT_ID !=0 GROUP BY t_sys_user_2_region.USER_ID \n" +
                ") AS regionName on regionName.USER_ID=visit.ENTOURAGE_ID\n");
        sb.append("LEFT JOIN(\n" +
                "SELECT t_sys_user.id,t_sys_user.JOB_STATE as jobState\n" +
                "FROM t_sys_user\n" +
                ") AS jobState ON jobState.id=visit.ENTOURAGE_ID\n");
        sb.append("LEFT JOIN (\n" +
                "SELECT t_sys_region.ID,t_sys_user_2_region.USER_ID as userId\n" +
                "FROM t_sys_user_2_region LEFT JOIN t_sys_region \n" +
                "ON t_sys_user_2_region.REGION_ID=t_sys_region.ID\n" +
                "WHERE t_sys_region.PARENT_ID = 0\n" +
                "GROUP BY t_sys_user_2_region.USER_ID \n" +
                ")AS region on region.userId=visit.userId\n");
        sb.append("LEFT JOIN(\n" +
                "SELECT t_sys_depart.id as departId, COUNT(1) AS countNo\n" +
                "FROM t_wrt_customer_visit \n" +
                "LEFT JOIN t_sys_user on t_sys_user.ID=t_wrt_customer_visit.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role on t_sys_role.id=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name on t_sys_role.ROLE_NAME_ID=t_sys_role_name.id\n" +
                "LEFT JOIN t_sys_depart on t_sys_depart.id=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_user_leave on t_sys_user_leave.USER_ID=t_sys_user.ID\n" +
                "WHERE t_sys_role_name.`NAME`= '大区经理' AND t_wrt_customer_visit.VISIT_MODE=1 \n");
        sb.append("and \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_wrt_customer_visit.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("GROUP BY t_sys_depart.id\n" +
                ")AS countNo ON countNo.departId=visit.departId\n" +
                "ORDER BY userId,departName,visitCount desc\n" +
                ") AS tmp\n" +
                "ORDER BY countNo desc,managerName,visitCount desc) AS obj, (SELECT @rownum := 0 ,@rowtotal := NULL ) r \n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getCustomerByManage(String dateTime, String userId, String entourage, String todayTime, Integer spacing) {
        int len = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT  \n ");
        sb.append(" t_sys_user.`name` AS managerName, \n");
        sb.append(" manager.`JOB_STATE`  AS jobState, \n ");
        sb.append(" manager.ENTRY_DATE  AS entryDate,   \n ");
        sb.append(" visit.ENTOURAGE AS entourageName,  \n ");
        sb.append(" customer.TEL AS customerTel,  \n ");
        sb.append(" visit.ID AS customerId,  \n ");
        sb.append(" visit.ARRIVALS_TIME AS arrivalsTime,\n");
        sb.append(" visit.LEAVE_TIME AS leaveTime, \n");
        sb.append(" customer.`NAME` AS customerName,  \n ");
        sb.append(" customer.WECHAT AS customerWechat,  \n ");
        sb.append(" customer.CUSTOMER_TYPE AS customerType,  \n ");
        sb.append(" customer.CUSTOMER_LEVEL AS customerLevel,  \n ");
        sb.append(" IFNULL(CEIL(CEILING(UNIX_TIMESTAMP(visit.LEAVE_TIME)-UNIX_TIMESTAMP(visit.ARRIVALS_TIME))/60),0) as time\n ");
        sb.append(" FROM `t_wrt_customer_visit` AS visit  \n ");
        sb.append(" LEFT JOIN t_sys_user ON t_sys_user.id=visit.CREATE_USER_ID\n ");
        sb.append(" LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID  \n ");
        sb.append(" LEFT JOIN t_sys_role_name on t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID \n ");
        sb.append(" LEFT JOIN t_sys_depart on t_sys_role.DEPART_ID = t_sys_depart.ID \n ");
        sb.append(" LEFT JOIN t_sys_user AS manager ON manager.ID=visit.ENTOURAGE_ID\n ");
        sb.append(" LEFT JOIN t_wrt_customer AS customer ON visit.CUSTOMER_ID=customer.ID\n ");
        sb.append(" where\n ");
        switch (len) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("visit.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("visit.REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("visit.REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(visit.REPORT_DATE) \n");
                break;
        }
        sb.append(" AND t_sys_role_name.`NAME`='大区经理' \n ");
        sb.append(" AND visit.VISIT_MODE=1 \n ");
        sb.append(" AND visit.CREATE_USER_ID=" + "'" + userId + "'");
        sb.append(" AND visit.ENTOURAGE=" + "'" + entourage + "'\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public JSONObject getCustomerVisitById(String id) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT * FROM v_wrt_customer_visit WHERE id='" + id + "'\n");
        return super.queryForJsonObject(sb.toString());
    }

    public List<JSONObject> getCompanyByUserId(String dateTime, Integer spacing, String todayTime, String companysId) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT * from (\n" +
                "SELECT company.*,managerName.managerName,managerName.salerManagerName,\n" +
                "IFNULL(userNow.regionManagerNum,0) as regionManagerNum,IFNULL(userTarget.managerTargetNum,0) as  managerTargetNum ,\n" +
                "IFNULL(visitCount.visitCount,0) AS visitCount,IFNULL(managerCount.managerCount,0) as managerCount\n" +
                "FROM  (SELECT t_sys_company.id AS companyId , t_sys_company.`NAME` as companyName \n" +
                "\tFROM t_sys_company WHERE 1=1 AND DEL_FLAG = 0 ) as company LEFT JOIN \n");
        sb.append("(SELECT v_sys_company.companyId,v_sys_company.managerName,v_sys_company.salerManagerName \n" +
                "FROM v_sys_company\n" +
                "WHERE 1=1 AND v_sys_company.companyDelFlag =0\n" +
                ") as managerName ON company.companyId  = managerName.companyId  \n");
        sb.append(" LEFT JOIN (SELECT t_sys_company.ID as companyId,COUNT(1) as complete from t_wrt_customer_visit \n" +
                "\tLEFT JOIN t_sys_user ON t_wrt_customer_visit.CREATE_USER_ID = t_sys_user.ID LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "  LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID \n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE 1=1 AND ");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_wrt_customer_visit.REPORT_DATE  BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append(" t_wrt_customer_visit.REPORT_DATE  LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append(" t_wrt_customer_visit.REPORT_DATE  > '" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date( t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("AND t_sys_role_name.`NAME` = '大区经理' and t_wrt_customer_visit.VISIT_MODE =1\n" +
                "  GROUP BY t_sys_company.ID ) as visit ON company.companyId = visit.companyId\n" +
                "LEFT JOIN  (SELECT t_sys_company.ID as companyId, \n" +
                "\tsum(t_sys_role.TARGET_NUMBER) as managerTargetNum FROM t_sys_role LEFT JOIN t_sys_depart \n" +
                "ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_role_name on t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID \n" +
                "LEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\tWHERE 1=1 AND t_sys_role_name.`NAME` = '大区经理' \n" +
                "AND t_sys_depart.DEL_FLAG = 0 and t_sys_role.DEL_FLAG = 0 and t_sys_role_name.DEL_FLAG = 0 AND t_sys_company.DEL_FLAG = 0\n" +
                "GROUP BY t_sys_company.ID \n" +
                ") as userTarget ON company.companyId = userTarget.companyId \n");
        sb.append("LEFT JOIN \n" +
                "(SELECT v_sys_user_for_report.companyId AS companyId, COUNT(1) as regionManagerNum FROM v_sys_user_for_report \n" +
                "WHERE 1=1 AND roleName = '大区经理'  AND v_sys_user_for_report.userId is not null  AND (v_sys_user_for_report.leaveDate IS NULL OR \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("v_sys_user_for_report.leaveDate  BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("v_sys_user_for_report.leaveDate  LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("v_sys_user_for_report.leaveDate  > '" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(v_sys_user_for_report.leaveDate) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(v_sys_user_for_report.leaveDate) \n");
                break;
        }
        sb.append(")GROUP BY v_sys_user_for_report.companyId ) as userNow ON company.companyId = userNow.companyId \n" +
                "LEFT JOIN(\n" +
                "SELECT t_sys_company.ID AS companyId,t_sys_company.`NAME` AS companyName,\n" +
                "COUNT(t_wrt_customer_visit.VISIT_MODE) AS visitCount\n" +
                "FROM t_wrt_customer_visit \n" +
                "LEFT JOIN t_sys_user on t_sys_user.ID=t_wrt_customer_visit.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role on t_sys_role.id=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name on t_sys_role.ROLE_NAME_ID=t_sys_role_name.id\n" +
                "LEFT JOIN t_sys_depart on t_sys_depart.id=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company on t_sys_depart.COMPANY_ID=t_sys_company.id\n" +
                "LEFT JOIN t_sys_user_leave on t_sys_user_leave.USER_ID=t_sys_user.ID\n" +
                "WHERE t_sys_role_name.`NAME`= '大区经理' AND t_wrt_customer_visit.VISIT_MODE=1 \n");
        sb.append("and \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_wrt_customer_visit.REPORT_DATE  BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE  LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("AND (t_sys_user_leave.LEAVE_DATE IS NULL OR \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_sys_user_leave.LEAVE_DATE  BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_sys_user_leave.LEAVE_DATE  LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_sys_user_leave.LEAVE_DATE > '" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_sys_user_leave.LEAVE_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_user_leave.LEAVE_DATE) \n");
                break;
        }
        sb.append(")GROUP BY t_sys_company.id) AS visitCount on visitCount.companyId=company.companyId\n" +
                "LEFT JOIN(\n" +
                "SELECT COUNT(managerCount.ENTOURAGE) AS managerCount,managerCount.companyId FROm(\n" +
                "SELECT t_wrt_customer_visit.ENTOURAGE,t_sys_company.ID AS companyId\n" +
                "FROM t_wrt_customer_visit \n" +
                "LEFT JOIN t_sys_user on t_sys_user.ID=t_wrt_customer_visit.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role on t_sys_role.id=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name on t_sys_role.ROLE_NAME_ID=t_sys_role_name.id\n" +
                "LEFT JOIN t_sys_depart on t_sys_depart.id=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company on t_sys_depart.COMPANY_ID=t_sys_company.id\n" +
                "LEFT JOIN t_sys_user_leave on t_sys_user_leave.USER_ID=t_sys_user.ID\n" +
                "WHERE t_sys_role_name.`NAME`= '大区经理' AND t_wrt_customer_visit.VISIT_MODE=1\n" +
                "AND  t_wrt_customer_visit.ENTOURAGE_ID IS not null\n");
        sb.append("and \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_wrt_customer_visit.REPORT_DATE  BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE  LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("AND (t_sys_user_leave.LEAVE_DATE IS NULL OR \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_sys_user_leave.LEAVE_DATE  BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_sys_user_leave.LEAVE_DATE  LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_sys_user_leave.LEAVE_DATE > '" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_sys_user_leave.LEAVE_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_user_leave.LEAVE_DATE) \n");
                break;
        }
        sb.append(")GROUP BY ENTOURAGE) AS managerCount\n" +
                "GROUP BY managerCount.companyId\n" +
                ")as managerCount ON managerCount.companyId=company.companyId\n" +
                ") as obj\n");
        sb.append("WHERE obj.companyId in (" + companysId + ")\n");
        sb.append("ORDER BY obj.visitCount desc\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    //公司下大客户部门
    public List<JSONObject> getForBigCustomerDepart(String target, Integer type, String dateTime, Integer spacing, String userId, String todayTime, String companyId) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT obj.* , CASE \n" +
                " WHEN @rowtotal = obj.averageComplete THEN \n" +
                "  @rownum \n" +
                " WHEN @rowtotal := obj.averageComplete THEN \n" +
                " @rownum :=@rownum + 1 \n" +
                " WHEN @rowtotal = 0 THEN \n" +
                " @rownum :=@rownum + 1 \n" +
                "  END AS rownum \n" +
                "  FROM( \n");
        sb.append("SELECT depart.*,bigManager.bigManagerName,IFNULL(visit.complete,0) as complete,IFNULL(target.target,0) as target,\n" +
                "IFNULL(userNow.regionManagerNum,0) as regionManagerNum,IFNULL(userTarget.managerTargetNum,0) as  managerTargetNum ,IFNULL(leaveManager.leaveNumber,0) as leaveNumber\n");
        if (length == 10) {
            sb.append(",(select IFNULL(TRUNCATE(complete/(regionManagerNum-IFNULL(leaveNumber,0)),2),0.00)) as  averageComplete\n");
        } else {
            sb.append(",(select IFNULL(TRUNCATE(complete/regionManagerNum,2),0.00)) as  averageComplete\n");
        }
        sb.append(",(select IFNULL(TRUNCATE(complete/target*100,2),0.00)) as  completionRate\n" +
                "FROM (SELECT t_sys_depart.COMPANY_ID as companyId,t_sys_depart.ID as departId,t_sys_depart.`NAME` as departName,t_sys_company.`NAME` as companyName\n" +
                "\tFROM t_sys_depart \n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_depart.COMPANY_ID\n" +
                "WHERE 1=1 AND (t_sys_depart.`NAME` LIKE '%大客户%' or t_sys_company.`NAME` LIKE '%大客户%') AND t_sys_depart.DEL_FLAG = 0 AND t_sys_company.DEL_FLAG =0  ) as depart \n");
        sb.append("LEFT JOIN (SELECT t_sys_depart.ID as departId,v_sys_user.name as bigManagerName\n" +
                "FROM v_sys_user LEFT JOIN t_sys_depart ON t_sys_depart.id=v_sys_user.departId\n" +
                "LEFT JOIN t_sys_company on v_sys_user.companyId=t_sys_company.ID \n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=v_sys_user.roleNameId\n" +
                "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID=v_sys_user.id\n" +
                "WHERE 1=1\n" +
                "and t_sys_company.DEL_FLAG =0 and t_sys_depart.DEL_FLAG =0 and t_sys_role_name.DEL_FLAG =0\n" +
                "and (t_sys_depart.`NAME` LIKE '%大客户%' OR t_sys_company.`NAME` like '%大客户%') AND t_sys_role_name.`NAME` = '大客户总监'   AND (t_sys_user_leave.LEAVE_DATE IS NULL OR   \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_sys_user_leave.LEAVE_DATE BETWEEN'" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_sys_user_leave.LEAVE_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_sys_user_leave.LEAVE_DATE > '" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_sys_user_leave.LEAVE_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_user_leave.LEAVE_DATE) \n");
                break;
        }
        sb.append(") ) as bigManager ON depart.departId = bigManager.departId LEFT JOIN \n" +
                "(SELECT t_sys_depart.ID as departId,COUNT(1) as complete from t_wrt_customer_visit \n" +
                "\tLEFT JOIN t_sys_user ON t_wrt_customer_visit.CREATE_USER_ID = t_sys_user.ID LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "  LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE 1=1 AND   \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" REPORT_DATE BETWEEN'" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(REPORT_DATE) \n");
                break;
        }
        sb.append("AND t_sys_role_name.`NAME` = '大客户经理' and t_wrt_customer_visit.VISIT_MODE = " + type + "  \n" +
                "  GROUP BY t_sys_depart.ID ) as visit ON depart.departId = visit.departId\n" +
                "LEFT JOIN (\tSELECT t_sys_depart.id as departId,SUM(t_sys_target." + target + ") as target FROM t_sys_target\n" +
                "\tLEFT JOIN t_sys_user ON t_sys_target.USER_ID = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "  LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE 1=1 AND t_sys_role_name.`NAME` = '大客户经理'  \tAND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" t_sys_target.TARGET_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_sys_target.TARGET_DATE  LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_sys_target.TARGET_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_sys_target.TARGET_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_target.TARGET_DATE) \n");
                break;
        }
        sb.append("GROUP BY t_sys_depart.ID ) as target ON depart.departId = target.departId LEFT JOIN  \n" +
                "(SELECT t_sys_depart.ID as departId, \n" +
                "\tt_sys_role.TARGET_NUMBER as managerTargetNum FROM t_sys_role LEFT JOIN t_sys_depart \n" +
                "ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_role_name on t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID \n" +
                "LEFT JOIN t_sys_company ON t_sys_company.id=t_sys_depart.COMPANY_ID\n" +
                "\tWHERE 1=1  AND t_sys_role_name.`NAME` = '大客户经理' \n" +
                "\tAND (t_sys_depart.`NAME` like '%大客户%' OR t_sys_company.`NAME` LIKE '%大客户%') AND t_sys_depart.DEL_FLAG = 0 and t_sys_role.DEL_FLAG = 0 and t_sys_role_name.DEL_FLAG = 0 ) as userTarget ON depart.departId = userTarget.departId LEFT JOIN \n" +
                "(SELECT departId, COUNT(1) as regionManagerNum FROM v_sys_user_for_report \n" +
                "WHERE 1=1  AND roleName = '大客户经理' AND (v_sys_user_for_report.leaveDate IS NULL OR  \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" v_sys_user_for_report.leaveDate BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("v_sys_user_for_report.leaveDate LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("v_sys_user_for_report.leaveDate > '" + dateTime + "' \n");
                int year = Integer.parseInt(todayTime.substring(0, 4));
                int month = Integer.parseInt(todayTime.substring(5, 7));
                sb.append("and v_sys_user_for_report.entryDate <= '" + year + "-" + month + "-" + DateUtils.getLastDayOfMonth(year, month) + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(v_sys_user_for_report.leaveDate) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(v_sys_user_for_report.leaveDate) \n");
                break;
        }
        sb.append(")GROUP BY departId ) as userNow ON depart.departId = userNow.departId \n" +
                "LEFT JOIN (SELECT t_sys_role.DEPART_ID as departId,COUNT(1) as leaveNumber \n" +
                "FROM (SELECT t_sys_leave.USER_ID as userId,COUNT(1) as leaveCount FROM t_sys_leave WHERE\n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" LEAVE_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("LEAVE_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("LEAVE_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(LEAVE_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(LEAVE_DATE) \n");
                break;
        }
        sb.append("GROUP BY t_sys_leave.USER_ID) as signLeave LEFT JOIN t_sys_user ON signLeave.userId = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "WHERE t_sys_role_name.`NAME` ='大客户经理' GROUP BY t_sys_role.DEPART_ID\n" +
                " )as leaveManager ON depart.departId = leaveManager.departId \n");
        if (companyId == null) {
            sb.append("WHERE 1=1 AND companyId in (SELECT companyId FROM v_sys_part_time_for_report WHERE userid in ('" + userId + "')) \n");
        } else {
            sb.append("WHERE 1=1 AND companyId in ('" + companyId + "') \n");
        }
        sb.append("ORDER BY averageComplete DESC ) as obj,   (SELECT @rownum := 0 ,@rowtotal := NULL) r \n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    //大客户拜访集团级别
    public List<JSONObject> getBigCustomerForGroupByUserId(String target, Integer type, String dateTime, Integer spacing, String userId, String todayTime, String companyId) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT obj.* , CASE \n" +
                " WHEN @rowtotal = obj.averageComplete THEN \n" +
                "  @rownum \n" +
                " WHEN @rowtotal := obj.averageComplete THEN \n" +
                " @rownum :=@rownum + 1 \n" +
                " WHEN @rowtotal = 0 THEN \n" +
                " @rownum :=@rownum + 1 \n" +
                "  END AS rownum \n" +
                "  FROM( \n" +
                "SELECT company.*,managerName.managerName,managerName.salerManagerName,IFNULL(visit.complete,0) as complete,IFNULL(target.target,0) as target,\n" +
                "IFNULL(userNow.regionManagerNum,0) as regionManagerNum,IFNULL(userTarget.managerTargetNum,0) as  managerTargetNum ,IFNULL(leaveManager.leaveNumber,0) as leaveNumber\n" +
                " ,(select IFNULL(TRUNCATE(complete/target*100,2),0.00)) as  completionRate\n");
        if (length == 10) {
            sb.append(",(select IFNULL(TRUNCATE(complete/(regionManagerNum-IFNULL(leaveNumber,0)),2),0.00)) as  averageComplete\n");
        } else {
            sb.append(",(select IFNULL(TRUNCATE(complete/regionManagerNum,2),0.00)) as  averageComplete\n");

        }
        sb.append("FROM\n" +
                " (SELECT t_sys_company.id AS companyId,t_sys_company.`NAME` as companyName \n" +
                "\tFROM t_sys_company \n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.COMPANY_ID=t_sys_company.ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.DEPART_ID=t_sys_depart.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE 1=1 AND t_sys_company.DEL_FLAG = 0 AND t_sys_depart.DEL_FLAG =0 and t_sys_role.DEL_FLAG=0 and t_sys_role_name.DEL_FLAG =0\n" +
                "AND t_sys_role_name.FRONT_FLAG=1\n" +
                "GROUP BY COMPANY_ID\n" +
                ") as company LEFT JOIN \n" +
                "(SELECT\n" +
                "\t`t_sys_company`.`ID` AS `companyId`,\n" +
                "\t`user_manager`.`name` AS `managerName`,\n" +
                "\t`user_sale_manager`.`name` AS `salerManagerName`\n" +
                "FROM `t_sys_company`\n" +
                "\t\t\tLEFT JOIN `v_sys_user` `user_manager` ON (`t_sys_company`.`ID` = `user_manager`.`companyId`\n" +
                "AND `user_manager`.`roleName` = '大客户总经理' AND `user_manager`.`delFlag` = 0)\n" +
                "\t\tLEFT JOIN `v_sys_user` `user_sale_manager` ON (\t`t_sys_company`.`ID` = `user_sale_manager`.`companyId`\n" +
                "\tAND \t`user_sale_manager`.`roleName` = '大客户总监'\n" +
                "AND `user_sale_manager`.`delFlag` = 0)\n" +
                "WHERE t_sys_company.DEL_FLAG=0 GROUP BY `t_sys_company`.`ID`\n" +
                ") as managerName ON company.companyId  = managerName.companyId    \n" +
                " LEFT JOIN \n" +
                "(SELECT t_sys_company.ID as companyId,COUNT(1) as complete from t_wrt_customer_visit \n" +
                "\tLEFT JOIN t_sys_user ON t_wrt_customer_visit.CREATE_USER_ID = t_sys_user.ID LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "  LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID \n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tWHERE 1=1 AND  \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" t_wrt_customer_visit.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("AND t_sys_role_name.`NAME` = '大客户经理' and t_wrt_customer_visit.VISIT_MODE =" + type + "\n" +
                "  GROUP BY t_sys_company.ID ) as visit ON company.companyId = visit.companyId\n" +
                "LEFT JOIN (\tSELECT t_sys_company.id as companyId,SUM(t_sys_target." + target + ") as target FROM t_sys_target\n" +
                "\tLEFT JOIN t_sys_user ON t_sys_target.USER_ID = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "  LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\tWHERE 1=1 AND t_sys_depart.DEL_FLAG = 0 and t_sys_role.DEL_FLAG = 0 and t_sys_role_name.DEL_FLAG = 0 AND t_sys_company.DEL_FLAG = 0\n" +
                "\tAND t_sys_role_name.`NAME` = '大客户经理' AND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" t_sys_target.TARGET_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_sys_target.TARGET_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_sys_target.TARGET_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_sys_target.TARGET_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_target.TARGET_DATE) \n");
                break;
        }
        sb.append("GROUP BY t_sys_company.ID ) as target ON company.companyId = target.companyId\n" +
                "LEFT JOIN  (SELECT t_sys_company.ID as companyId, \n" +
                "\tsum(t_sys_role.TARGET_NUMBER) as managerTargetNum FROM t_sys_role LEFT JOIN t_sys_depart \n" +
                "ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_role_name on t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID \n" +
                "LEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "\tWHERE 1=1 AND t_sys_role_name.`NAME` = '大客户经理' \n" +
                "AND t_sys_depart.DEL_FLAG = 0 and t_sys_role.DEL_FLAG = 0 and t_sys_role_name.DEL_FLAG = 0 AND t_sys_company.DEL_FLAG = 0\n" +
                "GROUP BY t_sys_company.ID \n" +
                ") as userTarget ON company.companyId = userTarget.companyId \n" +
                "LEFT JOIN \n" +
                "(SELECT v_sys_user_for_report.companyId AS companyId, COUNT(1) as regionManagerNum FROM v_sys_user_for_report \n" +
                "WHERE 1=1 AND roleName = '大客户经理' AND (v_sys_user_for_report.leaveDate IS NULL OR  \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" v_sys_user_for_report.leaveDate BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("v_sys_user_for_report.leaveDate LIKE '" + dateTime + "%' \n");
                    int year = Integer.parseInt(todayTime.substring(0, 4));
                    int month = Integer.parseInt(todayTime.substring(5, 7));
                    sb.append("and v_sys_user_for_report.entryDate <= '" + year + "-" + month + "-" + DateUtils.getLastDayOfMonth(year, month) + "' \n");
                }
                break;
            case 10:
                sb.append("v_sys_user_for_report.leaveDate > '" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(v_sys_user_for_report.leaveDate) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(v_sys_user_for_report.leaveDate) \n");
                break;
        }
        sb.append(")GROUP BY v_sys_user_for_report.companyId ) as userNow ON company.companyId = userNow.companyId \n" +
                "LEFT JOIN (SELECT t_sys_company.id as companyId,COUNT(1) as leaveNumber FROM \n" +
                "\t(SELECT t_sys_leave.USER_ID as userId,COUNT(1) as leaveCount FROM t_sys_leave WHERE 1=1 AND (t_sys_leave.LEAVE_DATE IS NULL OR\n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_sys_leave.LEAVE_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_sys_leave.LEAVE_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_sys_leave.LEAVE_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_sys_leave.LEAVE_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_leave.LEAVE_DATE) \n");
                break;
        }
        sb.append(")GROUP BY t_sys_leave.USER_ID ) as signLeave\n" +
                "\tLEFT JOIN t_sys_user ON signLeave.userId = t_sys_user.ID\n" +
                "\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\tLEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "\tLEFT JOIN t_sys_company ON t_sys_depart.COMPANY_ID = t_sys_company.ID\n" +
                "WHERE t_sys_role_name.`NAME` = '大客户经理' \n" +
                "AND t_sys_depart.DEL_FLAG = 0 and t_sys_role.DEL_FLAG = 0 and t_sys_role_name.DEL_FLAG = 0 AND t_sys_company.DEL_FLAG = 0\n" +
                "\tGROUP BY t_sys_company.ID\n" +
                " )as leaveManager ON company.companyId = leaveManager.companyId ");
        sb.append("WHERE company.companyId in (" + companyId + ")");
        sb.append(" ORDER BY averageComplete DESC ) as obj,   (SELECT @rownum := 0 ,@rowtotal := NULL) r \n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    //公司下全部大客户拜访
    public List<JSONObject> getAllBigCustomerManagerByCompanyId(String target, Integer type, String dateTime, Integer spacing, String todayTime, String companyId) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT \n" +
                "DISTINCT obj.*, CASE \n" +
                "WHEN @rowtotal = obj.averageComplete THEN \n" +
                "@rownum \n" +
                "WHEN @rowtotal := obj.averageComplete THEN \n" +
                "@rownum :=@rownum + 1 \n" +
                "WHEN @rowtotal = 0 THEN \n" +
                "@rownum :=@rownum + 1 \n" +
                "END AS rownum \n" +
                "FROM( \n" +
                "SELECT depart.regionId , depart.departName ,depart.managerName,depart.bigManagerName,depart.userId,\n" +
                "IFNULL(b_sys_target.target, 0) AS target, \n" +
                "IFNULL(t_sys_complete.complete, 0) AS complete, \n" +
                "IFNULL(t_sys_sign.sign, 0) AS signManagerNum,\n" +
                "(SELECT \n" +
                "IFNULL(TRUNCATE (complete / sign, 2),0.00) \n" +
                ") AS averageComplete, \n" +
                "(select IFNULL(TRUNCATE(complete/target*100,2),0.00)) as  completionRate\n" +
                "FROM \n" +
                "(SELECT obj.*,obj1.bigManagerName\n" +
                "FROM (\n" +
                "SELECT v_sys_part_time_for_report.userName as managerName,v_sys_part_time_for_report.userId as userId ,t_sys_depart.`NAME` as departName,t_sys_region.ID AS regionId\n" +
                "FROM v_sys_part_time_for_report LEFT JOIN t_sys_depart on t_sys_depart.ID=.v_sys_part_time_for_report.departId\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID= v_sys_part_time_for_report.userId\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId=t_sys_role_name.ID\n" +
                "LEFT JOIN  t_sys_user_leave ON t_sys_user_leave.USER_ID=v_sys_part_time_for_report.userId\n" +
                "WHERE 1=1 and t_sys_depart.DEL_FLAG =0 and t_sys_region.DEL_FLAG=0 and t_sys_role_name.DEL_FLAG=0\n" +
                "and t_sys_region.PARENT_ID!=0 AND v_sys_part_time_for_report.companyId=\"" + companyId + "\"\n" +
                "AND t_sys_role_name.`NAME` LIKE '%大客户经理%'\n" +
                "AND (t_sys_user_leave.LEAVE_DATE IS NULL OR \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_sys_user_leave.LEAVE_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_sys_user_leave.LEAVE_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_sys_user_leave.LEAVE_DATE > '" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_sys_user_leave.LEAVE_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_user_leave.LEAVE_DATE) \n");
                break;
        }
        sb.append(") ) as obj LEFT JOIN\n" +
                "(SELECT v_sys_part_time_for_report.userName as bigManagerName,t_sys_user_2_region.REGION_ID as regionId\n" +
                "FROM v_sys_part_time_for_report \n" +
                "LEFT JOIN t_sys_user_2_region ON v_sys_part_time_for_report.userId=t_sys_user_2_region.USER_ID\n" +
                "LEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId=t_sys_role_name.ID\n" +
                "WHERE 1=1 and t_sys_role_name.DEL_FLAG=0   and t_sys_role_name.`NAME` like '%大客户总监%' \n" +
                "AND v_sys_part_time_for_report.companyId=\"" + companyId + "\"\n" +
                "and v_sys_part_time_for_report.jobState!=1) as obj1 ON obj1.regionId=obj.regionId\n\n");
        sb.append("GROUP BY obj.userId ) as depart \n" +
                "LEFT JOIN ( SELECT  USER_ID, SUM(" + target + ") AS target  FROM  t_sys_target  WHERE  \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("TARGET_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("TARGET_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("TARGET_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(TARGET_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(TARGET_DATE) \n");
                break;
        }
        sb.append("GROUP BY  USER_ID ) b_sys_target ON depart.userId = b_sys_target.USER_ID  \n" +
                "LEFT JOIN (  SELECT  CREATE_USER_ID,  SUM(VISIT_MODE = " + type + ") AS complete \n" +
                "FROM  t_wrt_customer_visit WHERE \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(REPORT_DATE) \n");
                break;
        }
        sb.append("GROUP BY CREATE_USER_ID ) AS t_sys_complete ON depart.userId = t_sys_complete.CREATE_USER_ID\n" +
                "LEFT JOIN ( SELECT  USER_ID, COUNT(t_sys_sign_in.SIGN_DATE) AS sign FROM  t_sys_sign_in  WHERE \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("SIGN_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("SIGN_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("SIGN_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(SIGN_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(SIGN_DATE) \n");
                break;
        }
        sb.append("GROUP BY  USER_ID ) AS t_sys_sign ON t_sys_sign.USER_ID = depart.userId ORDER BY averageComplete desc,complete desc\n" +
                ") AS obj, (SELECT @rownum := 0 ,@rowtotal := NULL ) r \n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    //报表--今日数据
    public List<JSONObject> getForPersonalDayByUserIdAndDateTime(Integer type, String dateTime, String userId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT  obj.id as customerId,obj.tel,obj.arrivalsImg,obj.name,obj.wechat,obj.newCustomer,obj.leaveImg\n" +
                " ,obj.arrivalsTime,obj.leaveTime,obj.intentionModel,obj.serverCondition," +
                "obj.time,obj.arrivalsLongitude,obj.arrivalsLatitude,obj.otherCondition," +
                "obj.otherVisitType,obj.serialNo,obj.accessoriesChargesCondition,obj.hoursChargesCondition," +
                "obj.serverCustomerType,obj.debtCondition,obj.lettersCondition\n" +
                ",t_sys_sign_in.LONGITUDE as longitude,t_sys_sign_in.LATITUDE as latitude,obj.entourage,obj.customerType,obj.customerCredit,obj.customerLevel\n" +
                ",CASE WHEN\n" +
                "FLOOR(CEIL(CEILING(UNIX_TIMESTAMP(obj.arrivalsTime)-UNIX_TIMESTAMP(t_sys_sign_in.CREATE_TIME))/60)/60) <> 0 \n" +
                "THEN \n" +
                "CONCAT(FLOOR(CEIL(CEILING(UNIX_TIMESTAMP(obj.arrivalsTime)-UNIX_TIMESTAMP(t_sys_sign_in.CREATE_TIME))/60)/60),'小时',(CEIL(CEILING(UNIX_TIMESTAMP(obj.arrivalsTime)-UNIX_TIMESTAMP(t_sys_sign_in.CREATE_TIME))/60)%60),'分')\n" +
                "ELSE CONCAT((CEIL(CEILING(UNIX_TIMESTAMP(obj.arrivalsTime)-UNIX_TIMESTAMP(t_sys_sign_in.CREATE_TIME))/60)%60),'分')\n" +
                "END as timeInterval\n" +
                ",round(CEIL(CEILING(UNIX_TIMESTAMP(obj.arrivalsTime)-UNIX_TIMESTAMP(t_sys_sign_in.CREATE_TIME))/60)/60,2) as hours\n" +
                "FROM(\n" +
                "SELECT b.TEL AS tel,\n" +
                "b.NAME AS name,b.WECHAT AS wechat, \n" +
                "a.ID AS id,a.REPORT_DATE AS date,a.ARRIVALS_TIME AS arrivalsTime,a.CREATE_USER_ID,a.ARRIVALS_IMG AS arrivalsImg,\n" +
                "a.LEAVE_TIME AS leaveTime,b.CUSTOMER_TYPE as customerType,b.CUSTOMER_LEVEL as customerLevel,\n" +
                "IFNULL(CEIL(CEILING(UNIX_TIMESTAMP(a.LEAVE_TIME)-UNIX_TIMESTAMP(a.ARRIVALS_TIME))/60),0) as time \n" +
                ",a.ARRIVALS_LONGITUDE as arrivalsLongitude,a.ARRIVALS_LATITUDE AS arrivalsLatitude,b.OTHER_CONDITION as otherCondition\n" +
                ",a.LEAVE_IMG,b.CREATE_TIME\n" +
                ",CASE when (b.CREATE_TIME is  null OR left(b.CREATE_TIME,10)<>'" + dateTime + "') THEN 0 else 1 END as newCustomer\n" +
                ",a.entourage,a.LEAVE_IMG as leaveImg,\n" +
                "a.CUSTOMER_CREDIT AS customerCredit,\n" +
                "a.`OTHER_VISIT_TYPE` AS `otherVisitType`,\n" +
                "a.`SERIALNO` AS `serialNo`,\n" +
                "\ta.`ACCESSORIES_CHARGES_CONDITION` AS `accessoriesChargesCondition`,\n" +
                "\ta.`HOURS_CHARGES_CONDITION` AS `hoursChargesCondition`,\n" +
                "\ta.`INTENTION_MODEL` AS `intentionModel`,\n" +
                "\ta.`SERVER_CONDITION` AS `serverCondition`,\n" +
                "\ta.`SERVER_CUSTOMER_TYPE` AS `serverCustomerType`,\n" +
                "\ta.`DEBT_CONDITION` AS `debtCondition`,\n" +
                "\ta.`LETTERS_CONDITION` AS `lettersCondition`\n" +
                " FROM \n" +
                "`t_wrt_customer_visit` as a  \n" +
                "LEFT JOIN t_wrt_customer as b ON a.CUSTOMER_ID = b.ID  \n" +
                "LEFT JOIN t_sys_sign_in ON a.CREATE_USER_ID = t_sys_sign_in.USER_ID AND a.REPORT_DATE=t_sys_sign_in.SIGN_DATE\n" +
                "WHERE a.CREATE_USER_ID='" + userId + "' AND \n" +
                "a.VISIT_MODE = " + type + " \n" +
                "AND a.REPORT_DATE='" + dateTime + "' \n" +
                " ) as obj \n" +
                "LEFT JOIN (SELECT min(t_wrt_customer_visit.ARRIVALS_TIME) as minTime FROM t_wrt_customer_visit WHERE 1=1 \n" +
                "AND t_wrt_customer_visit.REPORT_DATE='" + dateTime + "' \n" +
                "AND t_wrt_customer_visit.CREATE_USER_ID='" + userId + "' AND \n" +
                "t_wrt_customer_visit.VISIT_MODE = 1 \n" +
                "GROUP BY t_wrt_customer_visit.REPORT_DATE\n" +
                ")as minTime ON minTime.minTime=obj.arrivalsTime\n" +
                "LEFT JOIN t_sys_sign_in ON obj.CREATE_USER_ID=t_sys_sign_in.USER_ID AND left(minTime.minTime,10)=t_sys_sign_in.sign_date\n" +
                "ORDER BY obj.date,obj.arrivalsTime,obj.leaveTime DESC\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    //判断哪些大区是可以点击查看的
    public List<JSONObject> getForDepartByUserIdForClick(String userId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT replace(t_sys_region.`NAME`,'大区','') as regionName\n" +
                "FROM t_sys_user_2_region LEFT JOIN t_sys_region ON t_sys_region.id=t_sys_user_2_region.REGION_ID\n" +
                "WHERE t_sys_user_2_region.USER_ID='" + userId + "' and t_sys_region.DEL_FLAG =0\n" +
                "AND t_sys_region.PARENT_ID =0\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    //PC版本今日数据的第一条面访记录
    public List<JSONObject> getFirstVisitForPC(String userId, String dateTime, Integer spacing) {
        int len = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT  obj.id as customerId,obj.tel,obj.name,obj.wechat,obj.date\n" +
                " ,obj.arrivalsTime,obj.leaveTime,obj.time,obj.arrivalsLongitude,obj.arrivalsLatitude,obj.otherCondition\n" +
                ",t_sys_sign_in.LONGITUDE as longitude,t_sys_sign_in.LATITUDE as latitude,obj.customerType,obj.customerLevel\n" +
                ",CASE WHEN\n" +
                "FLOOR(CEIL(CEILING(UNIX_TIMESTAMP(obj.arrivalsTime)-UNIX_TIMESTAMP(t_sys_sign_in.CREATE_TIME))/60)/60) <> 0 \n" +
                "THEN \n" +
                "CONCAT(FLOOR(CEIL(CEILING(UNIX_TIMESTAMP(obj.arrivalsTime)-UNIX_TIMESTAMP(t_sys_sign_in.CREATE_TIME))/60)/60),'小时',(CEIL(CEILING(UNIX_TIMESTAMP(obj.arrivalsTime)-UNIX_TIMESTAMP(t_sys_sign_in.CREATE_TIME))/60)%60),'分')\n" +
                "ELSE CONCAT((CEIL(CEILING(UNIX_TIMESTAMP(obj.arrivalsTime)-UNIX_TIMESTAMP(t_sys_sign_in.CREATE_TIME))/60)%60),'分')\n" +
                "END as timeInterval\n" +
                "FROM(\n" +
                "SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "\t(\n" +
                "SELECT b.TEL AS tel,\n" +
                "b.NAME AS name, \n" +
                "b.WECHAT AS wechat, \n" +
                "a.ID AS id,a.REPORT_DATE AS date,a.ARRIVALS_TIME AS arrivalsTime,a.CREATE_USER_ID,\n" +
                "a.LEAVE_TIME AS leaveTime,b.CUSTOMER_TYPE as customerType,b.CUSTOMER_LEVEL as customerLevel,\n" +
                "IFNULL(CEIL(CEILING(UNIX_TIMESTAMP(a.LEAVE_TIME)-UNIX_TIMESTAMP(a.ARRIVALS_TIME))/60),0) as time \n" +
                ",a.ARRIVALS_LONGITUDE as arrivalsLongitude,a.ARRIVALS_LATITUDE AS arrivalsLatitude,b.OTHER_CONDITION as otherCondition\n" +
                "FROM \n" +
                "`t_wrt_customer_visit` as a  \n" +
                "LEFT JOIN t_wrt_customer as b ON a.CUSTOMER_ID = b.ID  \n" +
                "LEFT JOIN t_sys_sign_in ON a.CREATE_USER_ID = t_sys_sign_in.USER_ID AND a.REPORT_DATE=t_sys_sign_in.SIGN_DATE\n");
        sb.append("WHERE a.CREATE_USER_ID='" + userId + "' AND \n");
        sb.append("a.VISIT_MODE = 1 \n");
        switch (len) {
            case 7:
                sb.append("AND LEFT(a.REPORT_DATE,7) like '" + dateTime + "' \n");
                break;
            case 10:
                sb.append("AND a.REPORT_DATE='" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("and'" + dateCut + "'>=date(a.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(a.REPORT_DATE) \n");
                break;
        }
        sb.append("GROUP BY\n" +
                "\t\t\ta.REPORT_DATE,\n" +
                "\t\t\ta.ARRIVALS_TIME\n" +
                "\t) AS c\n" +
                "GROUP BY\n" +
                "\tc.date ) as obj \n" +
                "LEFT JOIN (SELECT min(t_wrt_customer_visit.ARRIVALS_TIME) as minTime FROM t_wrt_customer_visit WHERE 1=1 \n");
        switch (len) {
            case 7:
                sb.append("AND LEFT(t_wrt_customer_visit.REPORT_DATE,7) like '" + dateTime + "' \n");
                break;
            case 10:
                sb.append("AND t_wrt_customer_visit.REPORT_DATE='" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("and'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("AND t_wrt_customer_visit.CREATE_USER_ID='" + userId + "' AND \n");
        sb.append("t_wrt_customer_visit.VISIT_MODE = 1 \n");
        sb.append("GROUP BY t_wrt_customer_visit.REPORT_DATE\n" +
                ")as minTime ON minTime.minTime=obj.arrivalsTime\n" +
                "LEFT JOIN t_sys_sign_in ON obj.CREATE_USER_ID=t_sys_sign_in.USER_ID  AND left(minTime.minTime,10) =t_sys_sign_in.sign_date\n" +
                "ORDER BY obj.date,obj.arrivalsTime,obj.leaveTime DESC");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    //PC版本今日数据的第一条面访记录
    public List<JSONObject> getMonthVisitForPC(String userId, String dateTime) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT \n" +
                "v_wrt_visit.date,COUNT(v_wrt_visit.faceVisit) as faceVisit,COUNT(v_wrt_visit.telVisit) as telVisit\n" +
                "FROM\n" +
                "(SELECT\n" +
                "\tt_wrt_customer_visit.REPORT_DATE as date,\n" +
                "\tCASE\n" +
                "WHEN t_wrt_customer_visit.VISIT_MODE = 1 THEN\n" +
                "\t1\n" +
                "END AS faceVisit,\n" +
                " CASE\n" +
                "WHEN t_wrt_customer_visit.VISIT_MODE = 0 THEN\n" +
                "\t0\n" +
                "END AS telVisit\n" +
                "FROM\n" +
                "\tt_wrt_customer_visit\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND LEFT (\n" +
                "\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t7\n" +
                ") = '" + dateTime + "'\n" +
                "AND t_wrt_customer_visit.CREATE_USER_ID = '" + userId + "') as v_wrt_visit\n" +
                "GROUP BY v_wrt_visit.date\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> stratAndEndFaceVisit(String userIds, String dateTime, Integer spacing, String todayTime) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tsys_user.userId,\n" +
                "\tsys_user.userName,\n" +
                "\tsys_user.departId,\n" +
                "\tsys_user.departName,\n" +
                "\tsys_user.companyId,\n" +
                "\tsys_user.companyName,\n" +
                "\tsys_user.parentUserId,\n" +
                "\tsys_user.parentUserName,\n" +
                "\tarr_sign.reportDate,\n" +
                "\tarr_sign.arrivalsTime,\n" +
                "\tarr_sign.arrivalsLatitude,\n" +
                "\tarr_sign.arrivalsLongitude,\n" +
                "\tarr_sign.signTime,\n" +
                "\tarr_sign.latitude,\n" +
                "\tarr_sign.longitude,\n" +
                "\tarr_sign_out.leaveTime,\n" +
                "\tarr_sign_out.leaveLatitude,\n" +
                "\tarr_sign_out.leaveLongitude,\n" +
                "\tarr_sign_out.signOutTime,\n" +
                "\tarr_sign_out.signOutLatitude,\n" +
                "\tarr_sign_out.signOutLongitude,\n" +
                "\tCASE\n" +
                "WHEN FLOOR(\n" +
                "\tCEIL(\n" +
                "\t\tCEILING(\n" +
                "\t\t\tUNIX_TIMESTAMP(arr_sign.arrivalsTime) - UNIX_TIMESTAMP(arr_sign.signTime)\n" +
                "\t\t) / 60\n" +
                "\t) / 60\n" +
                ") <> 0 THEN\n" +
                "\tCONCAT(\n" +
                "\t\tFLOOR(\n" +
                "\t\t\tCEIL(\n" +
                "\t\t\t\tCEILING(\n" +
                "\t\t\t\t\tUNIX_TIMESTAMP(arr_sign.arrivalsTime) - UNIX_TIMESTAMP(arr_sign.signTime)\n" +
                "\t\t\t\t) / 60\n" +
                "\t\t\t) / 60\n" +
                "\t\t),\n" +
                "\t\t'小时',\n" +
                "\t\t(\n" +
                "\t\t\tCEIL(\n" +
                "\t\t\t\tCEILING(\n" +
                "\t\t\t\t\tUNIX_TIMESTAMP(arr_sign.arrivalsTime) - UNIX_TIMESTAMP(arr_sign.signTime)\n" +
                "\t\t\t\t) / 60\n" +
                "\t\t\t) % 60\n" +
                "\t\t),\n" +
                "\t\t'分'\n" +
                "\t)\n" +
                "ELSE\n" +
                "\tCONCAT(\n" +
                "\t\t(\n" +
                "\t\t\tCEIL(\n" +
                "\t\t\t\tCEILING(\n" +
                "\t\t\t\t\tUNIX_TIMESTAMP(arr_sign.arrivalsTime) - UNIX_TIMESTAMP(arr_sign.signTime)\n" +
                "\t\t\t\t) / 60\n" +
                "\t\t\t) % 60\n" +
                "\t\t),\n" +
                "\t\t'分'\n" +
                "\t)\n" +
                "END AS signTimeInterval,\n" +
                " CASE\n" +
                "WHEN FLOOR(\n" +
                "\tCEIL(\n" +
                "\t\tCEILING(\n" +
                "\t\t\tUNIX_TIMESTAMP(arr_sign_out.signOutTime) - UNIX_TIMESTAMP(arr_sign_out.leaveTime)\n" +
                "\t\t) / 60\n" +
                "\t) / 60\n" +
                ") <> 0 THEN\n" +
                "\tCONCAT(\n" +
                "\t\tFLOOR(\n" +
                "\t\t\tCEIL(\n" +
                "\t\t\t\tCEILING(\n" +
                "\t\t\t\t\tUNIX_TIMESTAMP(arr_sign_out.signOutTime) - UNIX_TIMESTAMP(arr_sign_out.leaveTime)\n" +
                "\t\t\t\t) / 60\n" +
                "\t\t\t) / 60\n" +
                "\t\t),\n" +
                "\t\t'小时',\n" +
                "\t\t(\n" +
                "\t\t\tCEIL(\n" +
                "\t\t\t\tCEILING(\n" +
                "\t\t\t\t\tUNIX_TIMESTAMP(arr_sign_out.signOutTime) - UNIX_TIMESTAMP(arr_sign_out.leaveTime)\n" +
                "\t\t\t\t) / 60\n" +
                "\t\t\t) % 60\n" +
                "\t\t),\n" +
                "\t\t'分'\n" +
                "\t)\n" +
                "ELSE\n" +
                "\tCONCAT(\n" +
                "\t\t(\n" +
                "\t\t\tCEIL(\n" +
                "\t\t\t\tCEILING(\n" +
                "\t\t\t\t\tUNIX_TIMESTAMP(arr_sign_out.signOutTime) - UNIX_TIMESTAMP(arr_sign_out.leaveTime)\n" +
                "\t\t\t\t) / 60\n" +
                "\t\t\t) % 60\n" +
                "\t\t),\n" +
                "\t\t'分'\n" +
                "\t)\n" +
                "END AS signOutTimeInterval\n" +
                "FROM\n");
        sb.append("(SELECT\n" +
                "\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\tt_wrt_customer_visit.CREATE_USER_ID\n" +
                "FROM\n" +
                "\tt_wrt_customer_visit\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "\t\t\tAND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" t_wrt_customer_visit.REPORT_DATE BETWEEN'" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE LIKE '%" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE like '%" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("AND t_wrt_customer_visit.CREATE_USER_ID IN (" + userIds + ")\n" +
                "GROUP BY\n" +
                "\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\tt_wrt_customer_visit.CREATE_USER_ID) as wrt_visit\n");
        sb.append("\tLEFT JOIN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tsysUser.*, userParent.parentUserId,\n" +
                "\t\t\tuserParent.parentUserName\n" +
                "\t\tFROM\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tv_sys_part_time_for_report.userId,\n" +
                "\t\t\t\t\tv_sys_part_time_for_report.userName,\n" +
                "\t\t\t\t\tv_sys_part_time_for_report.departId,\n" +
                "\t\t\t\t\tt_sys_depart.`NAME` AS departName,\n" +
                "\t\t\t\t\tv_sys_part_time_for_report.companyId,\n" +
                "\t\t\t\t\tt_sys_company.`NAME` AS companyName\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tv_sys_part_time_for_report\n" +
                "\t\t\t\tLEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId = t_sys_role_name.ID\n" +
                "\t\t\t\tLEFT JOIN t_sys_depart ON v_sys_part_time_for_report.departId = t_sys_depart.ID\n" +
                "\t\t\t\tLEFT JOIN t_sys_company ON v_sys_part_time_for_report.companyId = t_sys_company.ID\n" +
                "\t\t\t\tLEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID=v_sys_part_time_for_report.userId\n\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t1 = 1\n" +
                "\t\t\t\tAND t_sys_role_name.`NAME` in ('区域经理','区域组长')\n" +
                "\t\t\t\tAND v_sys_part_time_for_report.jobState != 1\n" +
                "\t\t\t\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "\t\t\t\tAND t_sys_company.DEL_FLAG = 0\n" +
                "\t\t\t\tAND v_sys_part_time_for_report.userId in (" + userIds + ")\n" +
                "AND (t_sys_user_leave.LEAVE_DATE IS NULL OR \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_sys_user_leave.LEAVE_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_sys_user_leave.LEAVE_DATE LIKE '%" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_sys_user_leave.LEAVE_DATE > '" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_sys_user_leave.LEAVE_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_user_leave.LEAVE_DATE) \n");
                break;
        }
        sb.append("\t\t\t\t)GROUP BY\n" +
                "\t\t\t\t\tv_sys_part_time_for_report.userId\n" +
                "\t\t\t) AS sysUser\n" +
                "\t\tLEFT JOIN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tv_sys_part_time_for_report.userId AS parentUserId,\n" +
                "\t\t\t\tv_sys_part_time_for_report.userName AS parentUserName,\n" +
                "\t\t\t\tv_sys_part_time_for_report.departId\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tv_sys_part_time_for_report\n" +
                "\t\t\tLEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId = t_sys_role_name.ID\n" +
                "\t\t\tLEFT JOIN t_sys_depart ON v_sys_part_time_for_report.departId = t_sys_depart.ID\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND t_sys_role_name.`NAME` = '大区经理'\n" +
                "\t\t\tAND v_sys_part_time_for_report.jobState != 1\n" +
                "\t\t\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "\t\t) AS userParent ON userParent.departId = sysUser.departId\n" +
                "\t) AS sys_user\n" +
                "\ton wrt_visit.CREATE_USER_ID=sys_user.userId\n\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\t*\n" +
                "\tFROM\n" +
                "\t\t(\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID AS userId,\n" +
                "\t\t\t\tt_wrt_customer_visit.REPORT_DATE AS reportDate,\n" +
                "\t\t\t\tt_wrt_customer_visit.ARRIVALS_TIME AS arrivalsTime,\n" +
                "\t\t\t\tt_wrt_customer_visit.ARRIVALS_LATITUDE AS arrivalsLatitude,\n" +
                "\t\t\t\tt_wrt_customer_visit.ARRIVALS_LONGITUDE AS arrivalsLongitude,\n" +
                "\t\t\t\tt_sys_sign_in.CREATE_TIME AS signTime,\n" +
                "\t\t\t\tt_sys_sign_in.LATITUDE AS latitude,\n" +
                "\t\t\t\tt_sys_sign_in.LONGITUDE AS longitude\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_wrt_customer_visit\n" +
                "\t\t\tLEFT JOIN t_sys_sign_in ON t_sys_sign_in.CREATE_USER_ID = t_wrt_customer_visit.CREATE_USER_ID\n" +
                "\t\t\tAND t_sys_sign_in.SIGN_DATE = t_wrt_customer_visit.REPORT_DATE\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND t_wrt_customer_visit.VISIT_MODE = 1\n" +
                "\t\t\tAND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" t_wrt_customer_visit.REPORT_DATE BETWEEN'" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE LIKE '%" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE like '%" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("\t\t\tAND t_wrt_customer_visit.CREATE_USER_ID in (" + userIds + ")\n" +
                "\t\t\tORDER BY\n" +
                "\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID,\n" +
                "\t\t\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t) AS wrt_customer\n" +
                "\tGROUP BY\n" +
                "\t\twrt_customer.userId,\n" +
                "\t\twrt_customer.reportDate\n" +
                ") AS arr_sign ON arr_sign.userId = sys_user.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\t*\n" +
                "\tFROM\n" +
                "\t\t(\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID AS userId,\n" +
                "\t\t\t\tt_wrt_customer_visit.REPORT_DATE AS reportDate,\n" +
                "\t\t\t\tt_wrt_customer_visit.LEAVE_TIME AS leaveTime,\n" +
                "\t\t\t\tt_wrt_customer_visit.LEAVE_LATITUDE AS leaveLatitude,\n" +
                "\t\t\t\tt_wrt_customer_visit.LEAVE_LONGITUDE AS leaveLongitude,\n" +
                "\t\t\t\tt_sys_sign_in.SIGN_OUT_TIME AS signOutTime,\n" +
                "\t\t\t\tt_sys_sign_in.SIGN_OUT_LATITUDE AS signOutLatitude,\n" +
                "\t\t\t\tt_sys_sign_in.SIGN_OUT_LONGITUDE AS signOutLongitude\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_wrt_customer_visit\n" +
                "\t\t\tLEFT JOIN t_sys_sign_in ON t_sys_sign_in.CREATE_USER_ID = t_wrt_customer_visit.CREATE_USER_ID\n" +
                "\t\t\tAND t_sys_sign_in.SIGN_DATE = t_wrt_customer_visit.REPORT_DATE\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\t1 = 1\n" +
                "\t\t\tAND t_wrt_customer_visit.VISIT_MODE = 1\n" +
                "\t\t\tAND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" t_wrt_customer_visit.REPORT_DATE BETWEEN'" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE LIKE '%" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE like '%" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("\t\t\tAND t_wrt_customer_visit.CREATE_USER_ID in (" + userIds + ")\n" +
                "\t\t\tORDER BY\n" +
                "\t\t\t\tt_wrt_customer_visit.CREATE_USER_ID,\n" +
                "\t\t\t\tt_wrt_customer_visit.REPORT_DATE,\n" +
                "\t\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t) AS wrt_customer_out\n" +
                "\tGROUP BY\n" +
                "\t\twrt_customer_out.userId,\n" +
                "\t\twrt_customer_out.reportDate\n" +
                ") AS arr_sign_out ON arr_sign_out.userId = sys_user.userId\n" +
                "WHERE sys_user.userId is not null\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getSignAndCustomerByUserId(String userId, String dateTime, Integer spacing, String todayTime) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tsysSign.*, obj.customerNum,\n" +
                "\tobj.arrivalsTime,\n" +
                "\tobj.leaveTime,\n" +
                "\tsysRegion.regionName\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID as userId,\n\n" +
                "\t\t\tt_wrt_customer_visit.REPORT_DATE AS reportDate,\n" +
                "\t\t\tCOUNT(1) AS customerNum,\n" +
                "\t\t\tmin(\n" +
                "\t\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t\t) AS arrivalsTime,\n" +
                "\t\t\tmax(\n" +
                "\t\t\t\tt_wrt_customer_visit.LEAVE_TIME\n" +
                "\t\t\t) AS leaveTime\n" +
                "\t\tFROM\n" +
                "\t\t\tt_wrt_customer_visit\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_wrt_customer_visit.CREATE_USER_ID = '" + userId + "'\n");
        sb.append("AND\n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append(" t_wrt_customer_visit.REPORT_DATE BETWEEN'" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE LIKE '%" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE like '%" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("\t\tAND t_wrt_customer_visit.VISIT_MODE = 1\n" +
                "\t\tGROUP BY\n" +
                "\t\t\tt_wrt_customer_visit.REPORT_DATE\n" +
                "\t\tORDER BY\n" +
                "\t\t\tt_wrt_customer_visit.REPORT_DATE\n" +
                "\t) AS obj\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_sign_in.CREATE_TIME AS signTime,\n" +
                "\t\tt_sys_sign_in.sign_date AS signDate,\n" +
                "\t\tt_sys_sign_in.SIGN_OUT_TIME AS signOutTime,\n" +
                "\t\tt_sys_sign_in.SIGN_IN_DISTANCE AS signInDistance,\n" +
                "\t\tt_sys_sign_in.SIGN_OUT_DISTANCE AS signOutDistance,\n" +
                "\t\tt_sys_sign_in.SIGN_IN_SPEED AS signInSpeed,\n" +
                "\t\tt_sys_sign_in.SIGN_OUT_SPEED AS signOutSpeed,\n" +
                "\t\tt_sys_sign_in.SIGN_IN_TIME_DIFF AS signInTimeDiff,\n" +
                "\t\tt_sys_sign_in.SIGN_OUT_TIME_DIFF AS signOutTimeDiff\n" +
                "\tFROM\n" +
                "\t\tt_sys_sign_in\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n");
        sb.append("AND\n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_sys_sign_in.sign_date BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_sys_sign_in.sign_date LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_sys_sign_in.sign_date like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_sys_sign_in.sign_date) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_sign_in.sign_date) \n");
                break;
        }
        sb.append("\tAND t_sys_sign_in.USER_ID = '" + userId + "'\n" +
                "\tGROUP BY\n" +
                "\t\tt_sys_sign_in.sign_date\n" +
                ") AS sysSign ON sysSign.signDate = obj.reportDate\n");
        sb.append("LEFT JOIN (SELECT t_sys_region.`NAME` as regionName,\n" +
                "t_sys_user_2_region.USER_ID as userId\n" +
                "FROM t_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID=t_sys_region.ID\n" +
                "WHERE 1=1\n" +
                "AND t_sys_user_2_region.USER_ID='" + userId + "'\n" +
                "AND t_sys_region.DEL_FLAG=0\n" +
                "AND t_sys_region.PARENT_ID!=0) as sysRegion ON sysRegion.userId=obj.userId\n");
        sb.append("order by sysSign.signDate desc\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    //综合报表-大区拜访-集团查看
    public List<JSONObject> getBigRegionManagerVisitForGroup(String dateTime, Integer spacing, String todayTime, String userId) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n" +
                "\tcompany.*, managerName.managerName,\n" +
                "\tmanagerName.salerManagerName,\n" +
                "\tuserNow.bigRegionManagerNum,\n" +
                "\tmanagerNum.managerCount,\n" +
                "\tcustomerNum.customerCount\n" +
                "FROM(\n" +
                "\tSELECT \n" +
                "    ID AS companyId,\n" +
                "    `NAME` AS companyName ,\n" +
                "    SORT_NO AS sortNo\n" +
                "  FROM t_sys_company\n" +
                "  WHERE 1 = 1\n" +
                "  AND ID != '201712161613432EAF27DBCCB7D0E6CA'\n" +
                "  AND ID IN(\n" +
                "    SELECT COMPANY_ID FROM t_sys_depart\n" +
                "    LEFT JOIN t_sys_role on t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "    LEFT JOIN t_sys_user ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "    WHERE t_sys_user.ID = '" + userId + "')\n" +
                "  OR PARENT_ID IN(\n" +
                "    SELECT COMPANY_ID FROM t_sys_depart\n" +
                "    LEFT JOIN t_sys_role on t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "    LEFT JOIN t_sys_user ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "    WHERE t_sys_user.ID = '" + userId + "')\n" +
                "  AND DEL_FLAG = 0\n" +
                "  ORDER BY SORT_NO\n" +
                "\t) AS company\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tv_sys_company.companyId,\n" +
                "\t\tv_sys_company.managerName,\n" +
                "\t\tv_sys_company.salerManagerName\n" +
                "\tFROM\n" +
                "\t\tv_sys_company\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND v_sys_company.companyDelFlag = 0\n" +
                ") AS managerName ON company.companyId = managerName.companyId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tv_sys_user.companyId AS companyId,\n" +
                "\t\tv_sys_user.companyName AS companyName,\n" +
                "\t\tCOUNT(1) AS bigRegionManagerNum\n" +
                "\tFROM\n" +
                "\t\tv_sys_user\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND roleName = '大区经理'\n" +
                "\tAND jobState != 1\n" +
                "\tGROUP BY\n" +
                "\t\tv_sys_user.companyId\n" +
                ") AS userNow ON company.companyId = userNow.companyId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_wrt_customer_visit.ENTOURAGE_ID AS entourageId,\n" +
                "\t\tt_wrt_customer_visit.COMPANY_ID AS companyId,\n" +
                "\t\tt_wrt_customer_visit.REPORT_DATE AS reportDate,\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID AS createUserId,\n" +
                "\t\tt_wrt_customer_visit.CUSTOMER_ID AS customerId,\n" +
                "\t\tCOUNT(\n" +
                "\t\t\tt_wrt_customer_visit.ENTOURAGE_ID\n" +
                "\t\t) AS managerCount\n" +
                "\tFROM\n" +
                "\t\tv_sys_user\n" +
                "\tLEFT JOIN t_wrt_customer_visit ON v_sys_user.id = t_wrt_customer_visit.CREATE_USER_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\t\tAND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_wrt_customer_visit.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("\tAND t_wrt_customer_visit.ENTOURAGE_ID IS NOT NULL\n" +
                "\tAND v_sys_user.roleName = '大区经理'\n" +
                "\tGROUP BY\n" +
                "\t\tt_wrt_customer_visit.ENTOURAGE_ID\n" +
                ") AS managerNum ON managerNum.companyId = company.companyId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_wrt_customer_visit.ENTOURAGE_ID AS entourageId,\n" +
                "\t\tt_wrt_customer_visit.COMPANY_ID AS companyId,\n" +
                "\t\tt_wrt_customer_visit.REPORT_DATE AS reportDate,\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID AS createUserId,\n" +
                "\t\tt_wrt_customer_visit.CUSTOMER_ID AS customerId,\n" +
                "\t\tCOUNT(\n" +
                "\t\t\t1\n" +
                "\t\t) AS customerCount\n" +
                "\tFROM\n" +
                "\t\tv_sys_user\n" +
                "\tLEFT JOIN t_wrt_customer_visit ON v_sys_user.id = t_wrt_customer_visit.CREATE_USER_ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\t\tAND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_wrt_customer_visit.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE = '" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("\n" +
                "\tAND v_sys_user.roleName = '大区经理'\n" +
                "\tGROUP BY\n" +
                "\t\tv_sys_user.companyId\n" +
                ") AS customerNum ON customerNum.companyId = company.companyId\n" +
                "GROUP BY company.companyId\n" +
                "ORDER BY\n" +
                "\tcompany.sortNo ");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    //综合报表-大区拜访-公司查看
    public List<JSONObject> getBigRegionManagerVisitForDepart(String userId, String dateTime, String visitType, String companyId, Integer spacing, String todayTime, String yesdayTime, Integer userType) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n" +
                "sysUser.ID as userId,\n" +
                "sysUser.`NAME` AS name,\n" +
                "replace(sysUser.departName,'大区','') AS departName,\n" +
                "IFNULL(plan.num1,0) AS planCount,\n" +
                "IFNULL(wrtVisit.num2,0) AS finishCount,\n" +
                "signIn.signCreateTime AS signInTime,\n" +
                "signIn.signOutTime AS signOutTime,\n" +
                "IFNULL(wrtVisit.signInTime,'-') AS firstFaceSignInTime,\n" +
                "IFNULL(wrtVisit.signOutTime,'-') AS endFaceSignOutTime,\n" +
                "IFNULL(visitE.ENTOURAGE,'-') as firstFaceName,\n" +
                "IFNULL(visitF.ENTOURAGE,'-') as endFaceName,\n" +
                "signIn.signInDistance AS signInDistance,\n" +
                "signIn.signInSpeed AS signInSpeed,\n" +
                "signIn.signInTimeDiff AS signInTimeDiff,\n" +
                "signIn.signOutDistance AS signOutDistance,\n" +
                "signIn.signOutSpeed AS signOutSpeed,\n" +
                "signIn.signOutTimeDiff AS signOutTimeDiff,\n" +
                "signIn.signState AS signState,\n" +
                "signIn.signInNum AS signInNum\n" +
                "FROM \n" +
                "(SELECT sysUser.ID ,sysUser.`NAME` ,sysUser.departName\n" +
                "FROM (SELECT * FROM(\n" +
                "SELECT t_sys_user.`NAME`,t_sys_user.ID,t_sys_role.DEPART_ID,t_sys_depart.`NAME` AS departName\n" +
                "FROM t_sys_user\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.id\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.id=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                "WHERE 1=1\n" +
                "AND t_sys_role.DEL_FLAG=0\n");
        if (userType == 0) {
            sb.append("AND t_sys_role_name.`NAME` ='大区经理'\n");
        } else {
            sb.append("AND t_sys_role_name.`NAME` ='区域经理'\n");
        }
        sb.append("AND JOB_STATE!=1\n" +
                "UNION \n" +
                "SELECT t_sys_user.`NAME`,t_sys_user.ID,t_sys_role.DEPART_ID,t_sys_depart.`NAME` AS departName\n" +
                "FROM t_sys_user\n" +
                "LEFT JOIN t_sys_user_2_role ON t_sys_user_2_role.USER_ID=t_sys_user.id\n" +
                "LEFT JOIN t_sys_role ON t_sys_user_2_role.ROLE_ID=t_sys_role.id\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.id=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID = t_sys_role.DEPART_ID\n" +
                "WHERE 1=1\n" +
                "AND t_sys_role.DEL_FLAG=0\n");
        if (userType == 0) {
            sb.append("AND t_sys_role_name.`NAME` ='大区经理'\n");
        } else {
            sb.append("AND t_sys_role_name.`NAME` ='区域经理'\n");
        }
        sb.append("AND JOB_STATE!=1\n" +
                ")as sysUser\n" +
                "GROUP BY sysUser.ID\n" +
                ")as sysUser\n" +
                "LEFT JOIN t_sys_depart ON sysUser.DEPART_ID=t_sys_depart.id\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.id=t_sys_depart.COMPANY_ID\n" +
                "WHERE 1=1\n" +
                "AND t_sys_depart.DEL_FLAG=0\n" +
                "AND t_sys_company.DEL_FLAG=0\n");
        if (null != companyId) {
            sb.append("\tAND t_sys_company.ID IN ('" + companyId + "')\n");
        } else {
            sb.append(" AND t_sys_company.ID IN (SELECT companyId FROM v_sys_user WHERE id= '" + userId + "')\n");
        }
        sb.append(") sysUser\n" +
                "LEFT JOIN (\n" +
                "SELECT t_wrt_customer_vist_plan.CREATE_USER_ID as userId ,\n" +
                "COUNT(t_wrt_customer_vist_plan.CREATE_USER_ID) as num1 FROM t_wrt_customer_vist_plan\n" +
                "WHERE 1=1\n");
        sb.append("AND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_wrt_customer_vist_plan.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_vist_plan.REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_vist_plan.REPORT_DATE = '" + yesdayTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_vist_plan.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_vist_plan.REPORT_DATE) \n");
                break;
        }
        sb.append("AND (t_wrt_customer_vist_plan.VISIT_METHOD is null OR t_wrt_customer_vist_plan.VISIT_METHOD=" + visitType + ")\n" +
                "GROUP BY t_wrt_customer_vist_plan.CREATE_USER_ID\n" +
                ") as plan ON plan.userId=sysUser.ID\n" +
                "LEFT JOIN (\n" +
                "SELECT t_wrt_customer_visit.CREATE_USER_ID as userId,\n" +
                "COUNT(t_wrt_customer_visit.CREATE_USER_ID) as num2,\n" +
                "MIN(t_wrt_customer_visit.ARRIVALS_TIME) AS signInTime,\n" +
                "MAX(t_wrt_customer_visit.LEAVE_TIME) AS signOutTime\n" +
                "FROM t_wrt_customer_visit\n" +
                "WHERE 1=1\n" +
                "AND (VISIT_MODE='" + visitType + "' OR VISIT_MODE IS NULL)\n");
        sb.append("AND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(REPORT_DATE) \n");
                break;
        }
        sb.append("GROUP BY t_wrt_customer_visit.CREATE_USER_ID\n" +
                ") as wrtVisit\n" +
                "ON wrtVisit.userId=sysUser.ID\n" +
                "LEFT JOIN (\n" +
                "SELECT\n" +
                "\tv_sys_sign_leave_2.userId AS userId,\n" +
                "\tv_sys_sign_leave_2.signCreateTime AS signCreateTime,\n" +
                "\tv_sys_sign_leave_2.signOutTime AS signOutTime,\n" +
                "\tv_sys_sign_leave_2.signInDistance AS signInDistance,\n" +
                "\tv_sys_sign_leave_2.signInSpeed AS signInSpeed,\n" +
                "\tv_sys_sign_leave_2.signInTimeDiff AS signInTimeDiff,\n" +
                "\tv_sys_sign_leave_2.signOutDistance AS signOutDistance,\n" +
                "\tv_sys_sign_leave_2.signOutSpeed AS signOutSpeed,\n" +
                "\tv_sys_sign_leave_2.signOutTimeDiff AS signOutTimeDiff,\n" +
                "\tv_sys_sign_leave_2.signState AS signState,\n" +
                "  COUNT(v_sys_sign_leave_2.userId) AS signInNum\n" +
                "FROM\n" +
                "\tv_sys_sign_leave_2\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND\n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("v_sys_sign_leave_2.signDate BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("v_sys_sign_leave_2.signDate LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("v_sys_sign_leave_2.signDate like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(v_sys_sign_leave_2.signDate) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(v_sys_sign_leave_2.signDate) \n");
                break;
        }
        sb.append("GROUP BY v_sys_sign_leave_2.userId\n" +
                ") as signIn\n" +
                "ON signIn.userId=sysUser.ID\n" +
                "LEFT JOIN t_wrt_customer_visit as visitE\n" +
                "ON visitE.ARRIVALS_TIME=wrtVisit.signInTime \n" +
                "AND visitE.CREATE_USER_ID=sysUser.ID\n" +
                "AND (visitE.VISIT_MODE='" + visitType + "' OR visitE.VISIT_MODE IS NULL)\n" +
                "LEFT JOIN t_wrt_customer_visit as visitF\n" +
                "ON visitF.LEAVE_TIME=wrtVisit.signOutTime \n" +
                "AND visitF.CREATE_USER_ID=sysUser.ID\n" +
                "AND (visitF.VISIT_MODE='" + visitType + "' OR visitF.VISIT_MODE IS NULL)\n" +
                "group by sysUser.ID\n" +
                "ORDER BY signIn.signCreateTime DESC\n ");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getBigRegionManagerVisitForDepart2(String userId, String dateTime, Integer spacing, String todayTime, String visitType, String companyId) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n" +
                " id as userId,\n" +
                "\t`name`,\n" +
                "\tdepartName,\n" +
                "\troleName,\n" +
                "\tcompanyId,\n" +
                "\tcompanyName,\n" +
                "  faceVisit.*\n" +
                "FROM\n" +
                "\tv_sys_user\n" +
                "LEFT JOIN (\n" +
                "  SELECT \n" +
                "  finCount.createUserId,\n" +
                "  IFNULL(finCount.finishCount,'-') AS finishCount,\n" +
                "  IFNULL(plan.planCount,'-') AS planCount,\n" +
                "  IFNULL(rightCount.signInCount,'-') AS signInNum,\n" +
                "  IFNULL(errorCount.errorCount,'-') AS errorNum\n" +
                "FROM\n" +
                "(\n" +
                "\tSELECT\n" +
                "    t_wrt_customer_visit.CREATE_USER_ID AS createUserId,\n" +
                "\t\tCOUNT(t_wrt_customer_visit.CREATE_USER_ID) AS finishCount\n" +
                "\tFROM t_wrt_customer_visit\n" +
                "\tWHERE 1 = 1\n" +
                "\t\tAND \n");
        // "\tAND t_wrt_customer_visit.REPORT_DATE LIKE '%2018-09%'\n" +
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_wrt_customer_visit.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("  AND t_wrt_customer_visit.VISIT_MODE = '" + visitType + "'\n");
        //"\tAND t_wrt_customer_visit.COMPANY_ID IN (SELECT companyId FROM v_sys_user WHERE id= '" + userId + "')\n" +
        if (null != companyId) {
            sb.append("\tAND t_wrt_customer_visit.COMPANY_ID IN ('" + companyId + "')\n");
        } else {
            sb.append(" AND t_wrt_customer_visit.COMPANY_ID IN (SELECT companyId FROM v_sys_user WHERE id= '" + userId + "')\n");
        }
        sb.append("\tGROUP BY t_wrt_customer_visit.CREATE_USER_ID\n" +
                ") AS finCount \n" +
                "LEFT JOIN(\n" +
                "\t\tSELECT\n" +
                "\t\tCREATE_USER_ID AS createUserId,\n" +
                "\t\tCOUNT(t_wrt_customer_vist_plan.ID) AS planCount\n" +
                "\tFROM t_wrt_customer_vist_plan\n" +
                "\tWHERE 1 = 1\n" +
                "\t\tAND \n");
        // "\tAND t_wrt_customer_vist_plan.REPORT_DATE LIKE '%2018-09%'\n" +
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_wrt_customer_vist_plan.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_vist_plan.REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_vist_plan.REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_vist_plan.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_vist_plan.REPORT_DATE) \n");
                break;
        }
        sb.append("\tAND t_wrt_customer_vist_plan.VISIT_METHOD= '" + visitType + "'\n" +
                "\tGROUP BY CREATE_USER_ID\n" +
                ") AS plan ON plan.createUserId = finCount.createUserId\n" +
                "LEFT JOIN(\n" +
                "\tSELECT\n" +
                "\t CREATE_USER_ID AS createUserId,\n" +
                "\t COUNT(CREATE_USER_ID) AS signInCount\n" +
                "\tFROM\n" +
                "\t\tt_sys_sign_in\n" +
                "\tWHERE 1 = 1\n" +
                "\t\tAND \n");
        //"\tAND SIGN_DATE LIKE '%2018-09%'\n" +
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("SIGN_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("SIGN_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("SIGN_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(SIGN_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(SIGN_DATE) \n");
                break;
        }
        sb.append("\tGROUP BY CREATE_USER_ID) AS rightCount ON rightCount.createUserId = plan.createUserId\n" +
                "LEFT JOIN(\n" +
                "\tSELECT\n" +
                "\t CREATE_USER_ID AS createUserId,\n" +
                "\t COUNT(CREATE_USER_ID) AS errorCount\n" +
                "\tFROM\n" +
                "\t\tt_sys_sign_in\n" +
                "\tWHERE 1 = 1\n" +
                "\t\tAND \n");
        // "\tAND SIGN_DATE LIKE '%2018-09%'\n" +
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("SIGN_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("SIGN_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("SIGN_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(SIGN_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(SIGN_DATE) \n");
                break;
        }
        sb.append("\tAND (\n" +
                "\t\tREPLACE(t_sys_sign_in.SIGN_IN_DISTANCE,'公里','')>=15\n" +
                "\t\tOR REPLACE(t_sys_sign_in.SIGN_OUT_DISTANCE,'公里','')>=15\n" +
                "\t\tOR substring(t_sys_sign_in.SIGN_IN_TIME_DIFF,locate('时',t_sys_sign_in.SIGN_IN_TIME_DIFF)+1,2)>=30\n" +
                "\t\tOR substring(t_sys_sign_in.SIGN_OUT_TIME_DIFF,locate('时',t_sys_sign_in.SIGN_OUT_TIME_DIFF)+1,2)>=30)\n" +
                "\tGROUP BY CREATE_USER_ID)AS errorCount ON errorCount.createUserId = plan.createUserId\n" +
                ") AS faceVisit\n" +
                "ON faceVisit.createUserId = v_sys_user.id\n" +
                "WHERE 1 = 1\n");
        //"AND companyId IN (SELECT companyId FROM v_sys_user WHERE id= '" + userId + "')\n" +
        if (null != companyId) {
            sb.append("\tAND companyId IN ('" + companyId + "')\n");
        } else {
            sb.append(" AND companyId IN (SELECT companyId FROM v_sys_user WHERE id= '" + userId + "')\n");
        }
        sb.append("AND roleName = '大区经理'\n" +
                "AND jobState != 1\n" +
                "ORDER BY companySortNo,departSortNo ");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    //综合报表-大区拜访-拜访计划查看
    public List<JSONObject> getBigRegionManagerPlanVisit(String userId, String dateTime, Integer spacing, String todayTime, String visitType, String yesdayTime) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n" +
                "\tt_wrt_customer_vist_plan.CREATE_USER_ID AS userId,\n" +
                "\tt_wrt_customer_vist_plan.REPORT_DATE AS reportDate,\n" +
                "  t_wrt_customer_vist_plan.VISIT_GOAL AS visitGoal,\n" +
                "  t_wrt_customer_vist_plan.CUSTOMER_NAME AS customerName,\n" +
                "  t_wrt_customer_vist_plan.ENTOURAGE AS entourage,\n" +
                "  t_wrt_customer_vist_plan.CUSTOMER_ADDRESS AS customerAddress,\n" +
                "  t_wrt_customer_vist_plan.W_ID AS weiXinId,\n" +
                "  t_wrt_customer_vist_plan.VISIT_METHOD AS visitMethod\n" +
                "FROM\n" +
                "\tt_wrt_customer_vist_plan\n" +
                "WHERE\n" +
                "\tCREATE_USER_ID = '" + userId + "'\n" +
                "\t\tAND \n");
        //"AND REPORT_DATE = '2018-09-30'\n" +
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("REPORT_DATE LIKE '%" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("REPORT_DATE='" + yesdayTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(REPORT_DATE) \n");
                break;
        }
        sb.append("\tAND (VISIT_METHOD= '" + visitType + "' OR VISIT_METHOD IS NULL)\n");
        sb.append("\tAND CUSTOMER_NAME IS NOT NULL ");
        sb.append("\tORDER BY REPORT_DATE DESC ");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    //综合报表-大区拜访-拜访完成查看
    public List<JSONObject> getBigRegionManagerfaceList(String userId, String dateTime, Integer spacing, String todayTime, String visitType) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n" +
                "  t_sys_should_holiday.SHOULD_DATE AS bigFaceTime,\n" +
                "  t_wrt_customer_visit.CUSTOMER_ID AS customerId,\n" +
                "\tt_wrt_customer_visit.REPORT_DATE AS reportDate,\n" +
                "  t_wrt_customer_visit.ENTOURAGE_ID AS entourageId,\n" +
                "\tt_wrt_customer_visit.ENTOURAGE AS entourage,\n" +
                "\tt_wrt_customer_visit.ID AS id,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_TIME AS bigArrivalsTime,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_ADDRESS AS bigArrivalsAddress,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_LATITUDE AS bigRegionLatitude,\n" +
                "\tt_wrt_customer_visit.ARRIVALS_LONGITUDE AS bigRegionLongitude,\n" +
                "t_wrt_customer_visit.ARRIVALS_IMG AS bigArrivalsImg,\n" +
                "t_wrt_customer_visit.OTHER_CONDITION AS otherCondition,\n" +
                "\tt_wrt_customer_visit.W_ID AS bigRegionId,\n" +
                "  regionVisit.W_ID AS regionId,\n" +
                "\tt_wrt_customer.`NAME` AS customerName,\n" +
                "\tt_wrt_customer.TEL AS customerTel,\n" +
                "\tt_wrt_customer.WECHAT AS customerWeChat,\n" +
                "  regionVisit.ARRIVALS_TIME AS arrivalsTime,\n" +
                "  regionVisit.ARRIVALS_ADDRESS AS arrivalsAddress,\n" +
                "  regionVisit.ARRIVALS_IMG AS arrivalsImg,\n" +
                "  regionVisit.ARRIVALS_LATITUDE AS regionLatitude,\n" +
                "  regionVisit.ARRIVALS_LONGITUDE AS regionLongitude,\n" +
                "\tIFNULL(\n" +
                "\t\t\t\tCEIL(\n" +
                "\t\t\t\t\tCEILING(\n" +
                "\t\t\t\t\t\tUNIX_TIMESTAMP(t_wrt_customer_visit.LEAVE_TIME) - UNIX_TIMESTAMP(t_wrt_customer_visit.ARRIVALS_TIME)\n" +
                "\t\t\t\t\t) / 60\n" +
                "\t\t\t\t) ,\n" +
                "\t\t\t\t0\n" +
                "\t ) AS timeDiff\n" +
                "FROM t_sys_should_holiday\n" +
                "LEFT JOIN t_wrt_customer_visit ON t_sys_should_holiday.SHOULD_DATE = t_wrt_customer_visit.REPORT_DATE \n" +
                "    AND t_wrt_customer_visit.VISIT_MODE = '" + visitType + "'\n" +
                "    AND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_wrt_customer_visit.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE = '" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("    AND t_wrt_customer_visit.CREATE_USER_ID = '" + userId + "'  \n" +
                "LEFT JOIN t_wrt_customer_visit AS regionVisit \n" +
                "    ON regionVisit.VISIT_MODE = '" + visitType + "'\n" +
                "    AND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("regionVisit.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("regionVisit.REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("regionVisit.REPORT_DATE = '" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(regionVisit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(regionVisit.REPORT_DATE) \n");
                break;
        }
        sb.append("    AND t_wrt_customer_visit.ENTOURAGE_ID = regionVisit.CREATE_USER_ID\n" +
                "    AND t_wrt_customer_visit.CUSTOMER_ID = regionVisit.CUSTOMER_ID\n" +
                "    AND t_wrt_customer_visit.CREATE_USER_ID = regionVisit.ENTOURAGE_ID\n" +
                "    AND t_wrt_customer_visit.REPORT_DATE = regionVisit.REPORT_DATE \n" +
                "LEFT JOIN t_wrt_customer ON t_wrt_customer.ID = t_wrt_customer_visit.CUSTOMER_ID\n" +
                "WHERE 1 = 1\n" +
                "AND t_sys_should_holiday.FRONT_FLAG = '1'\n" +
                "AND t_sys_should_holiday.SHOULD_DATE <= '" + todayTime + "'\n" +
                "AND \n");
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_sys_should_holiday.SHOULD_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_sys_should_holiday.SHOULD_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_sys_should_holiday.SHOULD_DATE = '" + dateTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_sys_should_holiday.SHOULD_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_sys_should_holiday.SHOULD_DATE) \n");
                break;
        }
        sb.append("ORDER BY \n" +
                "    t_sys_should_holiday.SHOULD_DATE DESC,\n" +
                "    t_wrt_customer_visit.ARRIVALS_TIME\n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    //综合报表-大区拜访-目标和完成查看
    public JSONObject getFaceTargetAndComplete(String userId, String dateTime, Integer spacing, String todayTime, String visitType, String yesdayTime) {
        int length = dateTime.length();
        String dateCut = null;
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n" +
                "  finish.createUserId,\n" +
                "  finish.`name`,\n" +
                "  IFNULL(finish.complete,'-') AS complete,\n" +
                "  IFNULL(plan.target,'-') AS target\n" +
                "FROM\n" +
                "(SELECT\n" +
                "  t_wrt_customer_visit.CREATE_USER_ID AS createUserId,\n" +
                "  t_sys_user.`name` AS name,\n" +
                "  COUNT(t_wrt_customer_visit.CREATE_USER_ID) AS complete\n" +
                "FROM t_sys_user\n" +
                "LEFT JOIN t_wrt_customer_visit ON t_sys_user.ID = t_wrt_customer_visit.CREATE_USER_ID\n" +
                "WHERE 1 = 1\n" +
                "AND \n");
        //"   t_wrt_customer_visit.REPORT_DATE = '2018-10-07'\n" +
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_wrt_customer_visit.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_visit.REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_visit.REPORT_DATE like '" + dateTime + "%' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_visit.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_visit.REPORT_DATE) \n");
                break;
        }
        sb.append("AND (t_wrt_customer_visit.VISIT_MODE = '" + visitType + "' OR t_wrt_customer_visit.VISIT_MODE IS NULL)\n" +
                "AND t_wrt_customer_visit.CREATE_USER_ID = '" + userId + "'\n" +
                "GROUP BY t_wrt_customer_visit.CREATE_USER_ID) AS finish\n" +
                "LEFT JOIN(\n" +
                "\t\tSELECT\n" +
                "\t\tCREATE_USER_ID AS createUserId,\n" +
                "\t\tCOUNT(t_wrt_customer_vist_plan.ID) AS target\n" +
                "\tFROM t_wrt_customer_vist_plan\n" +
                "\tWHERE 1 = 1\n" +
                "\tAND \n");
        //"     t_wrt_customer_vist_plan.REPORT_DATE = '2018-10-07'\n" +
        switch (length) {
            case 7:
                String today = todayTime.substring(0, 7);
                if (today.equals(dateTime)) {
                    sb.append("t_wrt_customer_vist_plan.REPORT_DATE BETWEEN '" + dateTime + "-01" + "' AND '" + todayTime + "' \n");
                } else {
                    sb.append("t_wrt_customer_vist_plan.REPORT_DATE LIKE '" + dateTime + "%' \n");
                }
                break;
            case 10:
                sb.append("t_wrt_customer_vist_plan.REPORT_DATE = '" + yesdayTime + "' \n");
                break;
            case 12:
                dateCut = dateTime.substring(0, 10);
                sb.append("'" + dateCut + "'>=date(t_wrt_customer_vist_plan.REPORT_DATE) \n");
                sb.append("and DATE_SUB('" + dateCut + "', INTERVAL " + spacing + " DAY) <= date(t_wrt_customer_vist_plan.REPORT_DATE) \n");
                break;
        }
        sb.append("\tAND (t_wrt_customer_vist_plan.VISIT_METHOD= '" + visitType + "' OR t_wrt_customer_vist_plan.VISIT_METHOD IS NULL)\n" +
                "  AND t_wrt_customer_vist_plan.CREATE_USER_ID = '" + userId + "'\n" +
                "  ) AS plan ON plan.createUserId = finish.createUserId\n" +
                "\n ");
        JSONObject jsonObject = super.queryForJsonObject(sb.toString());
        return jsonObject;
    }

    public List<JSONObject> getRMTodayVisit(String userId, String dateTime, String todayTime, String visitType, String yesterday) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tdepart.regionId,\n" +
                "\tdepart.departName,\n" +
                "\tdepart.managerName,\n" +
                "\tdepart.userId,\n" +
                "\tsysSign.signInDistance,\n" +
                "\tsysSign.signInSpeed,\n" +
                "\tsysSign.signState,\n" +
                "\tsysSign.signInTimeDiff,\n" +
                "\tsysSign.signTime,\n" +
                "\tvisitArrivals.arrivalsTime,\n" +
                "\tIFNULL(plan.num1, 0) AS plan,\n" +
                "\tIFNULL(t_sys_complete.complete, 0) AS complete,\n" +
                "\tIFNULL(visitE.ENTOURAGE, '无') AS firstFaceName\n" +
                "\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tv_sys_part_time_for_report.userName AS managerName,\n" +
                "\t\t\tv_sys_part_time_for_report.userId AS userId,\n" +
                "\t\t\tt_sys_depart.`NAME` AS departName,\n" +
                "\t\t\tt_sys_region.ID AS regionId,\n" +
                "\t\t\tv_sys_part_time_for_report.parentId AS parentId,\n" +
                "\t\t\tv_sys_part_time_for_report.departId\n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_part_time_for_report\n" +
                "\t\tLEFT JOIN t_sys_depart ON t_sys_depart.ID =.v_sys_part_time_for_report.departId\n" +
                "\t\tLEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID = v_sys_part_time_for_report.userId\n" +
                "\t\tLEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "\t\tLEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId = t_sys_role_name.ID\n" +
                "\t\tLEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID = v_sys_part_time_for_report.userId\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_depart.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_role_name.DEL_FLAG = 0\n" +
                "\t\tAND t_sys_region.PARENT_ID = 0\n" +
                "\t\tAND v_sys_part_time_for_report.companyId in (SELECT companyId FROM v_sys_user WHERE id= '" + userId + "')\n" +
                "\t\tAND (\n" +
                "\t\t\tt_sys_role_name.`NAME` IN (\n" +
                "\t\t\t\t'区域经理',\n" +
                "\t\t\t\t'区域组长'\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\tt_sys_user_leave.LEAVE_DATE IS NULL\n" +
                "\t\t\t\tOR t_sys_user_leave.LEAVE_DATE > '" + todayTime + "'\n" +
                "\t\t\t)\n" +
                "\t\t)\n" +
                "\t\tGROUP BY\n" +
                "\t\t\tv_sys_part_time_for_report.userId\n" +
                "\t) AS depart\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_wrt_customer_vist_plan.CREATE_USER_ID AS userId,\n" +
                "\t\tCOUNT(\n" +
                "\t\t\tt_wrt_customer_vist_plan.CREATE_USER_ID\n" +
                "\t\t) AS num1\n" +
                "\tFROM\n" +
                "\t\tt_wrt_customer_vist_plan\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_customer_vist_plan.REPORT_DATE = '" + yesterday + "'\n" +
                "\tAND (\n" +
                "\t\tt_wrt_customer_vist_plan.VISIT_METHOD IS NULL\n" +
                "\t\tOR t_wrt_customer_vist_plan.VISIT_METHOD = 1\n" +
                "\t)\n" +
                "\tGROUP BY\n" +
                "\t\tt_wrt_customer_vist_plan.CREATE_USER_ID\n" +
                ") AS plan ON plan.userId = depart.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tCREATE_USER_ID,\n" +
                "\t\tSUM(VISIT_MODE = " + visitType + ") AS complete\n" +
                "\tFROM\n" +
                "\t\tt_wrt_customer_visit\n" +
                "\tWHERE\n" +
                "\t\tREPORT_DATE = '" + dateTime + "'\n" +
                "\tGROUP BY\n" +
                "\t\tCREATE_USER_ID\n" +
                ") AS t_sys_complete ON depart.userId = t_sys_complete.CREATE_USER_ID\n" +
                "\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tv_sys_sign_leave_2.userId AS userId,\n" +
                "\t\tv_sys_sign_leave_2.signCreateTime AS signTime,\n" +
                "\t\tv_sys_sign_leave_2.signInDistance AS signInDistance,\n" +
                "\t\tv_sys_sign_leave_2.signInSpeed AS signInSpeed,\n" +
                "\t\tv_sys_sign_leave_2.signInTimeDiff AS signInTimeDiff,\n" +
                "\t\tv_sys_sign_leave_2.signState AS signState,\n" +
                "\t\tCOUNT(v_sys_sign_leave_2.userId) AS signInNum\n" +
                "\tFROM\n" +
                "\t\tv_sys_sign_leave_2\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND v_sys_sign_leave_2.signDate = '"+dateTime+"'\n" +
                "\tGROUP BY\n" +
                "\t\tv_sys_sign_leave_2.userId\n" +
                ") AS sysSign ON depart.userId = sysSign.userId\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID AS userId,\n" +
                "\t\tmin(\n" +
                "\t\t\tt_wrt_customer_visit.ARRIVALS_TIME\n" +
                "\t\t) AS arrivalsTime\n" +
                "\tFROM\n" +
                "\t\tt_wrt_customer_visit\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_wrt_customer_visit.VISIT_MODE = 1\n" +
                "\tAND t_wrt_customer_visit.REPORT_DATE = '" + dateTime + "'\n" +
                "\tGROUP BY\n" +
                "\t\tt_wrt_customer_visit.CREATE_USER_ID\n" +
                ") AS visitArrivals ON visitArrivals.userId = depart.userId\n" +
                "LEFT JOIN t_wrt_customer_visit AS visitE ON visitE.ARRIVALS_TIME = visitArrivals.arrivalsTime\n" +
                "AND visitE.CREATE_USER_ID = depart.userId\n" +
                "AND (\n" +
                "\tvisitE.VISIT_MODE = '1'\n" +
                "\tOR visitE.VISIT_MODE IS NULL\n" +
                ")\n" +
                "ORDER BY\n" +
                "\tsignTime DESC");
        return super.queryForJsonList(sb.toString());
    }
}
